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?

Besotted

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 … – Rubbish

Answer 1

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.

Flax

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

@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) – Flax

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

Answer 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

Ethelda

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 – Besotted

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

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