SR0001: Avoid SELECT * in stored procedures, views, and table-valued functions
RuleId |
SR0001 |
Category |
Microsoft.Design |
Breaking Change |
Non-Breaking |
Cause
One or more of your stored procedures, views, or table-valued functions contains SELECT *.
Rule Description
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.
How to Fix Violations
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 to Suppress Warnings
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.
Example
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