Using table parameter in dynamic sql

Question

Hi everyone,

I have a stored proc like this that throws the following error when I execute it

CREATE PROC Test  
  
AS   
  
DECLARE @Temp Table (ID int, Name Varchar(50))  
  
insert into @Temp  
VALUES   
(1, 'Adam'),  
(2, 'John'),  
(3, 'Andrew')  
  
DECLARE @sql NVARCHAR(MAX)  
SET  @sql = 'SELECT * FROM'+ @Temp  
  
EXEC(@Temp)  

Msg 1087, Level 15, State 2, Line 19
Must declare the table variable "@temp ".

Can someone help me figure this out.

Answer 1

Hi @Anonymous ，

Welcome to Microsoft Q&A!

You could also include the DDL of @temp in @alenzi in order to avoid this kind of error.

Please also refer below:

CREATE PROC Test   
AS   
BEGIN   
DECLARE @sql NVARCHAR(MAX)  
SET  @sql = N' DECLARE @Temp Table (ID int, Name Varchar(50))   
insert into @Temp  
VALUES   
(1, ''Adam''),  
(2, ''John''),  
(3, ''Andrew'')  
SELECT * FROM @Temp'   
EXEC(@sql)  
END   

Then call this procedure as below:

EXEC Test  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can use a temporary table instead:

CREATE PROC Test
AS 

CREATE TABLE #Temp (ID int, Name Varchar(50))

INSERT INTO #Temp
VALUES 
(1, 'Adam'),
(2, 'John'),
(3, 'Andrew')

SELECT * FROM #Temp

DROP TABLE #Temp