using Table variable with sp_executesql

Question

I have a query that contain a table variable:

DECLARE @Selects    XML ;
SET @Selects='<Selects><Select><Q_ID>1</Q_ID><Q_DESC>nima1</Q_DESC></Select><Select><Q_ID>2</Q_ID><Q_DESC>nima2</Q_DESC></Select><Select><Q_ID>3</Q_ID><Q_DESC>nima3</Q_DESC></Select></Selects>'

DECLARE @QuestionID     NVARCHAR(10);
SET @QuestionID='a5';

DECLARE @TblSelect  TABLE 
(
    Q_ID            INT,
    Q_DESC          NVARCHAR(500)
)

INSERT INTO @TblSelect
(
    Q_ID,Q_DESC
)
SELECT  Q_Select.value('(Q_ID)[1]', 'int') AS 'Q_ID',
        Q_Select.value('(Q_DESC)[1]', 'nvarchar(500)') AS 'Q_DESC'
FROM    @Selects.nodes('/Selects/Select') AS AllSelects(Q_Select) 

DECLARE @Query      NVARCHAR(4000);
SET @Query=N'SELECT Q_ID,COUNT(Q_ID) FROM @TblSelect LEFT OUTER JOIN tblbase tb ON @TblSelect.Q_ID = @Col_Select group by Q_ID';

EXECUTE sp_executesql @Query,@TblSelect,@Col_Select

How I can pass the table variable to my query?

I came here because I wanted to insert dynamic SQL into a table variable, but in that case, the reference can just be put in front: INSERT INTO @TabVar EXEC sp_executesql … — dakab
– dakab, Commented Jan 5, 2024 at 11:32

Andomar · Accepted Answer · 2011-09-07 07:42:13Z

60

Here's an example of how to pass a table-valued parameter to sp_executesql. The variable has to be passed readonly:

if exists (select * from sys.types where name = 'TestTableType')
    drop type TestTableType

create type TestTableType as table (id int)
go
declare @t TestTableType
insert @t select 6*7

exec sp_executesql N'select * from @var', N'@var TestTableType readonly', @t

This prints the Answer to the Ultimate Question of Life, the Universe, and Everything.

answered Sep 7, 2011 at 7:42

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DooDoo Over a year ago

you mean that there is no way to pass Table variable?Is this better than us temprory table?

Andomar Over a year ago

@Nima: You can pass a table variable, and in fact, this example does so. But the table variable must have a named type. So it has to be declare @t TestTableType (named type) and not declare @t (id int) (anonymous type)

alejandrob Over a year ago

Readonly means: NO to the question. Basically,executesql and table type can only be used as read-only.

Nighil · Accepted Answer · 2011-09-07 07:17:04Z

2

Try Table Type

DECLARE @Selects    XML ;
    SET @Selects='<Selects><Select><Q_ID>1</Q_ID><Q_DESC>nima1</Q_DESC></Select><Select><Q_ID>2</Q_ID><Q_DESC>nima2</Q_DESC></Select><Select><Q_ID>3</Q_ID><Q_DESC>nima3</Q_DESC></Select></Selects>'

    DECLARE @QuestionID     NVARCHAR(10);
    SET @QuestionID='a5';

    DECLARE TYPE TblSelect  AS TABLE 
    (
        Q_ID            INT,
        Q_DESC          NVARCHAR(500)
    )

    /* Declare a variable that references the type. */
    DECLARE @TblSelect 
    AS TblSelect ;

    INSERT INTO @TblSelect 
    (
        Q_ID,Q_DESC
    )

    SELECT  Q_Select.value('(Q_ID)[1]', 'int') AS 'Q_ID',
            Q_Select.value('(Q_DESC)[1]', 'nvarchar(500)') AS 'Q_DESC'
    FROM    @Selects.nodes('/Selects/Select') AS AllSelects(Q_Select) 

    DECLARE @Query      NVARCHAR(4000);
    SET @Query=N'SELECT Q_ID,COUNT(Q_ID) FROM @TblSelect LEFT OUTER JOIN tblbase tb ON @TblSelect.Q_ID = @Col_Select group by Q_ID';

    EXECUTE sp_executesql @Query,@TblSelect,@Col_Select

answered Sep 7, 2011 at 7:17

Nighil

4,1277 gold badges34 silver badges58 bronze badges

3 Comments

DooDoo Over a year ago

I got this Error: Msg 155, Level 15, State 2, Line 7 'TblSelect' is not a recognized CURSOR option and I don't know hat is wrong with my @Col_Select I got error for it too

Nighil Over a year ago

see this link for more referance msdn.microsoft.com/en-us/library/bb510489.aspx

lazarus Over a year ago

DECLARE TYPE TblSelect AS TABLE should be CREATE TYPE TblSelect AS TABLE

Collectives™ on Stack Overflow

using Table variable with sp_executesql

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related