I am building t-sql in a stored procedure dynamically. @Results is a table variable. The following code compiles in the stored proc, but .NET tells me that I have to declare the variable @results...although it is already declared as a table variable. Ex:
DECLARE @Results TABLE ( TempId int IDENTITY PRIMARY KEY, ProductId int NOT NULL ...etc.... )
SET @sql = 'INSERT INTO @Results ' + 'select ... '
EXEC ( @sql )
Is this not possible? Is there something else I need to do to this stored procedure? Thanks
Given the short description of what you're trying to do, you have to use a global temporary table and not a table variable because the table variable is only visible in the context of the stored procedure and not inside the query you are building.