Thursday, December 3, 2009

How to pass a table name as a parameter to a stored procedure

In order to pass table name along with some fields to a stored procedure that does insert, one has to take advantage of dynamic sql


CREATE PROCEDURE myProc
@TableName as VARCHAR(50),
@fieldlist as VARCHAR(100),
@ID as INT,
@Name as VARCHAR(50)
AS
DECLARE @sql VARCHAR(1000)
DECLARE @str NVARCHAR(1000)

SET @sql = 'INSERT INTO ' + @TableName + '(' + @fieldlist + ')' + ' Values(@ID,@Name)'
Select @str = CAST(@sql as NVarchar(1000))
EXECUTE sp_executesql @str,N'@ID INT,@Name VARCHAR(50)', @ID, @Name

GO


The above stored procedure can be called with the following


EXEC myProc 'Table1', 'ID, Name' ,1,'Taylor Lautner'
GO

No comments:

Post a Comment