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

 Posted by Rajnilari2015 on 9/30/2015 | Category: Sql Server Interview questions | Views: 396 | Points: 40
Answer:

We need to use Dynamic SQL Approach.

e.g

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 as: EXEC usp_PassTableVariable '[dbo].[tblLocation]'

LocationID	Latitude	Longitude

1 26.457904 80.320663


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response