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.

3 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