I need to pass a table var to sp_executesql statement. Do you know how can I pass the table variable to sp_executesql?
Here is how I pass the regular variable (not table variable) to sp_executesql
EXEC sp_executesql @statement, N'@Status INT',@Status
Typically you don't pass a table variable to execute SQL with sp_executesql. You make a statement up out of text and execute that. Like so:
IF OBJECT_ID('tempdb..#People') IS NOT NULL
DROP TABLE tempdb..#People
CREATE TABLE #People (PersonId INT IDENTITY, PersonName VARCHAR(128));
INSERT INTO #People (PersonName) VALUES ('Brett'), ('John'), ('Mark'), ('Shawn'), ('Ryan'), ('Kevin');
DECLARE @SQL NVARCHAR(Max) = 'Select * from #People'
EXEC sp_executesql @Sql
UPDATE 1-27-17
IF OBJECT_ID('tempdb..#People') IS NOT NULL DROP TABLE tempdb..#People
IF OBJECT_ID('tempdb..#People2') IS NOT NULL DROP TABLE tempdb..#People2
CREATE TABLE #People (PersonId INT IDENTITY, PersonName VARCHAR(128));
CREATE TABLE #People2 (PersonId INT IDENTITY(7,1), PersonName VARCHAR(128));
SET NOCOUNT ON;
INSERT INTO #People (PersonName) VALUES ('Brett'), ('John'), ('Mark'), ('Shawn'), ('Ryan'), ('Kevin');
INSERT INTO #People2 (PersonName) VALUES ('Emily'), ('Beth'), ('Jane'), ('Hannah');
--I. getting an output for a single output variable dynamically
--Say I just want to get Ryan by his Id dynamically and output it
--I need to define one or many parameters OUTSIDE the scope of the Dynamic Sql
DECLARE @Output VARCHAR(8)
DECLARE @PersonId INT = 5
--I then need to associate the parameters as an array, for the purposes of explanation I will use DIFFERENT NAMES you may use the same
DECLARE @ParmDefinition NVARCHAR(500) = N'@PersonIdInside Int, @OutputInside varchar(8) OUTPUT'
--I then use the names ABOVE in the dynamic sql
DECLARE @SQL NVARCHAR(Max) = N'Select @OutputInside = PersonName from #People Where PersonId = @PersonIdInside'
-- I then do the following AFTER the sp_executesql 1. The Dynamic sql nvarchar, 2. The params nvarchar 3. one or many variables and how they associate
EXEC sp_executesql @Sql, @ParmDefinition, @PersonIdInside = @PersonId, @OutputInside = @Output OUTPUT
-- I have an output so now it should show what I want
SELECT @Output
-- II. getting a result set dymamically to another record set or table OUTSIDE the scope of the internal
-- Create another table, I use a #table for example purposes
IF OBJECT_ID('tempdb..#Output') IS NOT NULL DROP TABLE tempdb..#Output
CREATE TABLE #Output (PersonId INT IDENTITY, PersonName VARCHAR(8))
--Get a truncated list for an 'in' statement later of person Id's in a variable
DECLARE @People NVarchar(32) = N'1, 5, 10'
--I then use the @People ABOVE in the dynamic sql putting it together and then do an 'insert statement first'
DECLARE @SQL2 NVARCHAR(Max) = N'Insert Into #Output SELECT PersonName FROM (SELECT * FROM #People UNION SELECT * FROM #People2) as x Where PersonId in (' + @People + ')'
--execute yields nothing
EXEC sp_executesql @Sql2
-- or does it?
Select *
From #Output
-- !!! WARNING !!!
-- With dynamic sql you cannot nest multiple dynamic sql statements inside of procs. EG: Proc1 cannot call Proc2 and both of them have dynamic sql in them. Engine limitation.
