How to pass table variable as a parameter in Sql Server Stored Procedure?

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 495
We need to use DynamicSQL approach

CREATE PROCEDURE usp_PassTableVariable @table_name SYSNAME
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT *
FROM '+ @table_name
EXEC sp_executesql @SQL
END


OR

CREATE PROCEDURE usp_PassTableVariable @table_name VARCHAR(100)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT *
FROM '+ @table_name
EXEC sp_executesql @SQL
END


Execute the store procedure:
EXEC usp_PassTableVariable '[dbo].[tblLocation]'


Result
LocationID	Latitude	Longitude
1 26.457904 80.320663


Hope this helps

Comments or Responses

Posted by: Anjaneyulu2015 on: 9/30/2015 Level:Starter | Status: [Member] | Points: 10
nice, i learnt something new

Login to post response