Tuesday, 10 August 2010

Passing a TABLE variable into dynamic SQL

A question popped up on StackOverflow today, asking how to pass a TABLE variable into a dynamic SQL statement in SQL Server 2008. I've previously blogged about table-valued parameters, comparing the approach of passing in a TABLE of values to a stored procedure to the techniques you'd have had to use in earlier versions of SQL Server (e.g. CSV or XML), but this specific question is worth a quick follow-up.

Passing a TABLE variable in to a dynamic SQL statement is straight forwards:
-- First define a custom TABLE type
CREATE TYPE IntegerTableType AS TABLE (ID INTEGER)
GO

-- Fill a var of that type with some test data
DECLARE @MyTable IntegerTableType
INSERT @MyTable VALUES (1),(2),(3)

-- Now this is how you pass that var into dynamic statement
EXECUTE sp_executesql N'SELECT * FROM @MyTable', 
    N'@MyTable IntegerTableType READONLY', 
    @MyTable

Job done.

4 comments:

  1. Adrian, is there a way to do the same in SQL Server 2005?

    ReplyDelete
  2. @saumya - no, Table Valued Parameters only came along in SQL Server 2008. If you check out my previous post, it takes you through the "workarounds" from previous versions: http://www.adathedev.co.uk/2010/02/sql-server-2008-table-valued-parameters.html

    ReplyDelete
  3. Hey.. nice short n crisp example.

    But I still have a doubt.
    My table variable contains both int as well as nvarchar fields so this IntegarTableType is throwing error.

    PFB the err :
    Operand type clash: table is incompatible with IntegerTableType

    Any inputs on this??

    Thanks in advance.

    ReplyDelete
  4. Short and sweet! Very interesting. It left me wondering how smartly SQL Server is able to overcome scoping issue for a table variable otherwise.

    ReplyDelete