Stored Procedures - Do you avoid using SELECT * when inserting data?

Last updated by Brook Jeynes [SSW] 7 months ago.See history

Using a statement like "INSERT tableName SELECT * FROM otherTable", makes your stored procedures vulnerable to failure. Once either of the two tables change, 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

Figure: 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

Figure: Good Example - Using concrete columns instead of * and provide an Else section to raise errors

We open source. Powered by GitHub