Export (0) Print
Expand All

SR0001: Avoid SELECT * in stored procedures, views, and table-valued functions

RuleId

SR0001

Category

Microsoft.Design

Breaking Change

Non-Breaking

One or more of your stored procedures, views, or table-valued functions contains SELECT *.

If you use a wildcard character in a stored procedure, view, or table-valued function to select all columns in a table or view, the number or shape of returned columns might change if the underlying table or view changes. The shape of a column is a combination of its type and size. This variance could cause problems in applications that consume the stored procedure, view, or table-valued function because those consumers will expect a different number of columns.

You can protect consumers of the stored procedure, view, or table-valued function from schema changes by replacing the wildcard character with a fully qualified list of column names. You can easily expand the wildcard character by using refactoring. For more information, see Expand Wildcard Characters in SELECT Statements.

When you fix the issues that this rule identifies, you prevent future breaks in the applications that rely on your code. You should not suppress this warning.

The following example first defines a table that is named [Table2] and then defines two stored procedures. The first procedure contains a SELECT *, which violates rule SR0001. The second procedure avoids SELECT * and explicitly lists the columns in the SELECT statement.

CREATE TABLE [dbo].[Table2] 
( 
[ID] INT NOT NULL IDENTITY(0, 1), 
[c1] INT NOT NULL , 
[Comment] VARCHAR (50)
)
ON [PRIMARY]

CREATE PROCEDURE [dbo].[procWithWarning]
AS 
BEGIN
-- Contains code that breaks rule SR0001
SELECT * 
FROM [dbo].[Table2] 
END

CREATE PROCEDURE [dbo].[procFixed]
AS 
BEGIN
-- Explicitly lists the column names in a SELECT statement
SELECT [dbo].[Table2].[ID], [dbo].[Table2].[c1], [dbo].[Table2].[Comment]
FROM [dbo].[Table2] 
END

Community Additions

ADD
Show:
© 2015 Microsoft