Home
Stored Procedures - Do you avoid using SELECT * when inserting data?
  v2.0 Posted at 13/11/2019 10:19 AM by Tiago Araujo
​Using a statement like "INSERT tableName SELECT * FROM otherTable", makes your stored procedures vulnerable to failure. Once either of the two tables changs, your stored procedure won't work. Not only that, when the inserting table has an identity column, such a statement will cause an error - "An explicit value for the identity column in table ParaRight can only be specified when a column list is used and IDENTITY_INSERT is ON."

​USE [ParaGreg]
GO
/****** Object: StoredProcedure [dbo].[procMove] Script Date: 08/08/2008 12:18:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procMove]
@id AS Char,
@direction AS INT
AS
IF @direction = 0
BEGIN
INSERT INTO ParaRight
SELECT * FROM ParaLeft
WHERE ParaID = @id
DELETE FROM ParaLeft
WHERE ParaID = @id
END
ELSE IF @direction = 1
BEGIN
INSERT INTO ParaLeft
SELECT * FROM ParaRight
WHERE ParaID = @id
DELETE FROM ParaRight
WHERE ParaID = @id
END

​Bad example: Using SELECT * when inserting data. Besides, this stored procedure should have an Else section to raise error when no condition is satisfied


USE [ParaGreg]
GO
/****** Object: StoredProcedure [dbo].[procMove] Script Date: 08/08/2008 12:18:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procMove]
@id AS Char,
@direction AS INT
AS
IF @direction = 0
BEGIN
INSERT INTO ParaRight
SELECT Col1,Col2 FROM ParaLeft
WHERE ParaID = @id
DELETE FROM ParaLeft
WHERE ParaID = @id
END
ELSE IF @direction = 1
BEGIN
INSERT INTO ParaLeft
SELECT * FROM ParaRight
WHERE ParaID = @id
DELETE FROM ParaRight
WHERE ParaID = @id
END
ELSE BEGIN PRINT "Please use a correct direction"
END

Good example: Using concrete columns instead of * and provide an Else section to raise errors​​

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: