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?
Answers. This is not possible. Cause the table variable is isolated from the scope of the dynamic SQL. You can only use temporary and normal tables.
It appears that you can't. You can execute extended stored procedure inside a function and, even though sp_executesql is an extended stored procedure (despite its name), it still generates the message "only functions and extended stored procedures can be executed within a function".
EXEC : EXEC/Execute is used to execute any stored procedure or character string. Mostly it is used to execute the stored procedure. 2. SP_ExecuteSQL: SP_ExecuteSQL is used to execute ad-hoc SQL statements so that they can be executed as parameterized statements.
The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With