Wednesday, September 18, 2013

How to use table name as a variable in stored procedure

Sometimes it would be nice to pass the table name as a variable in a select query, its possible by using dynamic query as shown below

create procedure
declare @tablename varchar(30)


create procedure dbo.Test
@tablename varchar(30),
@val int
as

DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * FROM ' + @table_name +
' WHERE Column1 = ''' + @val +  ''''


EXEC sp_executesql @cmd


exec Test

No comments:

Post a Comment