How to use table variable in a dynamic sql statement?

by

On SQL Server 2008+ it is possible to use Table Valued Parameters to pass in a table variable to a dynamic SQL statement as long as you don’t need to update the values in the table itself.

So from the code you posted you could use this approach for @TSku but not for @RelPro

Example syntax below.

CREATE TYPE MyTable AS TABLE 
( 
Foo int,
Bar int
);
GO


DECLARE @T AS MyTable;

INSERT INTO @T VALUES (1,2), (2,3)

SELECT *,
        sys.fn_PhysLocFormatter(%%physloc%%) AS [physloc]
FROM @T

EXEC sp_executesql
  N'SELECT *,
        sys.fn_PhysLocFormatter(%%physloc%%) AS [physloc]
    FROM @T',
  N'@T MyTable READONLY',
  @T=@T

The physloc column is included just to demonstrate that the table variable referenced in the child scope is definitely the same one as the outer scope rather than a copy.

More Related Contents:

  1. Efficiently convert rows to columns in sql server
  2. How can I get column names from a table in SQL Server?
  3. How can I select the first day of a month in SQL?
  4. Multi-statement Table Valued Function vs Inline Table Valued Function
  5. SQL Server Insert if not exists
  6. Select statement to find duplicates on certain fields
  7. Copy data into another table
  8. Rename column SQL Server 2008
  9. How do I see active SQL Server connections?
  10. EXISTS vs JOIN and use of EXISTS clause
  11. UPDATE if exists else INSERT in SQL Server 2008 [duplicate]
  12. Why is a UDF so much slower than a subquery?
  13. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  14. Alternate of lead lag function in SQL Server 2008
  15. UNIX_TIMESTAMP in SQL Server
  16. T-SQL: How to use parameters in dynamic SQL?
  17. SQL Server SELECT LAST N Rows
  18. The transaction log for the database is full
  19. Generate random int value from 3 to 6
  20. Add emoji / emoticon to SQL Server table
  21. SQL Server SELECT INTO @variable?
  22. How do I change db schema to dbo
  23. How to subtract 30 days from the current date using SQL Server
  24. How do I find the data directory for a SQL Server instance?
  25. Count records for every month in a year
  26. SQL Server: +(unary) operator on non-numeric Strings
  27. Use one CTE many times
  28. How to Select Every Row Where Column Value is NOT Distinct
  29. How to do a case sensitive GROUP BY?
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment