How to use table variable in a dynamic sql statement

Question

In my stored procedure I declared two table variables on top of my procedure  Now I am trying to use that table variable within a dynamic sql statement but I get this error at the time of execution of that procedure  I am using Sql Server 2008   This is how my query looks like    set  col name     Assoc Item                    Convert nvarchar 2    curr row1    set  sqlstat    update  RelPro set                    col name                      Select relsku From  TSku Where tid                     Convert nvarchar 2    curr row1       Where RowID                     Convert nvarchar 2    curr row    Exec  sqlstat     And I get the following errors    Must declare the table variable   RelPro   Must declare the table variable   TSku    I have tried to take the table outside of the string block of dynamic query but to no avail

User · Answer

Using Temp table solves the problem but I ran into issues using Exec so I went with the following solution of using sp executesql   Create TABLE  tempJoin   Old ID int  New ID int    declare  table name varchar 128    declare  strSQL nvarchar 3072    set  table name    Object    --build sql sting to execute set  strSQL  INSERT INTO    table name   SELECT    columns   FROM  tempJoin CJ                         Inner Join    table name   sourceTbl On CJ Old ID   sourceTbl Object ID     exec sp executesql  strSQL

User · Answer

I don t think that is possible  though refer to the update below   as far as I know a table variable only exists within the scope that declared it   You can  however  use a temp table  use the create table syntax and prefix your table name with the   symbol   and that will be accessible within both the scope that creates it and the scope of your dynamic statement   UPDATE  Refer to Martin Smith s answer for how to use a table-valued parameter to pass a table variable in to a dynamic SQL statement   Also note the limitation mentioned  table-valued parameters are read-only

User · Answer

You don t have to use dynamic SQL  update     R set     Assoc Item 1   CASE WHEN  curr row   1 THEN foo relsku ELSE Assoc Item 1 END      Assoc Item 2   CASE WHEN  curr row   2 THEN foo relsku ELSE Assoc Item 2 END      Assoc Item 3   CASE WHEN  curr row   3 THEN foo relsku ELSE Assoc Item 3 END      Assoc Item 4   CASE WHEN  curr row   4 THEN foo relsku ELSE Assoc Item 4 END      Assoc Item 5   CASE WHEN  curr row   5 THEN foo relsku ELSE Assoc Item 5 END          from      Select relsku From  TSku Where tid    curr row1  foo     CROSS JOIN      RelPro R Where      R RowID    curr row

User · Answer

On SQL Server 2008  it is possible to use Table Valued Parameters to pass in a table variable to a dynamic SQL statement as long as you don t need to update the values in the table itself   So from the code you posted you could use this approach for  TSku but not for  RelPro  Example syntax below   CREATE TYPE MyTable AS TABLE     Foo int  Bar int    GO   DECLARE  T AS MyTable   INSERT INTO  T VALUES  1 2    2 3   SELECT            sys fn PhysLocFormatter   physloc    AS  physloc  FROM  T  EXEC sp executesql   N SELECT            sys fn PhysLocFormatter   physloc    AS  physloc      FROM  T     N  T MyTable READONLY      T  T    The physloc column is included just to demonstrate that the table variable referenced in the child scope is definitely the same one as the outer scope rather than a copy

User · Answer

Here is an example of using a dynamic T-SQL query and then extracting the results should you have more than one column of returned values  notice the dynamic table name    DECLARE   strSQLMain nvarchar 1000    recAPD number key char 10        Census sub code varchar 1    recAPD field name char 100    recAPD table name char 100    NUMBER KEY varchar 10    if object id   Permits   dbo   myTempAPD Txt    is not null       DROP TABLE  Permits   dbo   myTempAPD Txt   CREATE TABLE  Permits   dbo   myTempAPD Txt         MyCol1  char 10  NULL       MyCol2  char 1  NULL        -- an example of what  strSQLMain is    strSQLMain   SELECT  recAPD number key    NUMBER KEY    Census sub code TEXT 029 FROM APD TXT0 WHERE Number Key    01-7212   SET  strSQLMain     INSERT INTO myTempAPD Txt SELECT  NUMBER KEY      rtrim  recAPD field name     FROM    rtrim  recAPD table name      WHERE Number Key        rtrim  Number Key               EXEC   strSQLMain    SELECT  recAPD number key   MyCol1   Census sub code   MyCol2 from  Permits   dbo   myTempAPD Txt   DROP TABLE  Permits   dbo   myTempAPD Txt

User · Answer

Your EXEC executes in a different context  therefore it is not aware of any variables that have been declared in your original context  You should be able to use a temp table instead of a table variable as shown in the simple demo below   create table  t  id int   declare  value nchar 1  set  value   N 1   declare  sql nvarchar max  set  sql   N insert into  t  id  values       value   N     exec   sql   select   from  t  drop table  t

User · Answer

Well  I figured out the way and thought to share with the people out there who might run into the same problem   Let me start with the problem I had been facing   I had been trying to execute a Dynamic Sql Statement that used two temporary tables I declared at the top of my stored procedure  but because that dynamic sql statment created a new scope  I couldn t use the temporary tables   Solution   I simply changed them to Global Temporary Variables and they worked   Find my stored procedure underneath   CREATE PROCEDURE RAFCustom Room GetRelatedProducts -- Add the parameters for the stored procedure here  PRODUCT SKU nvarchar 15    Null   AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements      SET NOCOUNT ON   IF OBJECT ID  tempdb    RelPro    U   IS NOT NULL BEGIN     DROP TABLE   RelPro END  Create Table   RelPro       RowID int identity 1 1       ID int      Item Name nvarchar max       SKU nvarchar max       Vendor nvarchar max       Product Img 180 nvarchar max       rpGroup int      Assoc Item 1 nvarchar max       Assoc Item 2 nvarchar max       Assoc Item 3 nvarchar max       Assoc Item 4 nvarchar max       Assoc Item 5 nvarchar max       Assoc Item 6 nvarchar max       Assoc Item 7 nvarchar max       Assoc Item 8 nvarchar max       Assoc Item 9 nvarchar max       Assoc Item 10 nvarchar max      Begin     Insert   RelPro ID  Item Name  SKU  Vendor  Product Img 180  rpGroup       Select distinct zp ProductID  zp Name  zp SKU           Select m Name From ZNodeManufacturer m nolock  Where m ManufacturerID   zp ManufacturerID            http   s0001 server com is sw11 DG               Select m Custom1 From ZNodeManufacturer m nolock  Where m ManufacturerID   zp ManufacturerID                  zp SKU     3  SC 3243    ep RoomID     From Product zp nolock  Inner Join RF ExtendedProduct ep nolock  On ep ProductID   zp ProductID     Where zp ActiveInd   1 And SUBSTRING zp SKU  1  2   lt  gt   GC  AND zp Name  lt  gt   PLATINUM  AND zp SKU    Case When  PRODUCT SKU Is Not Null Then  PRODUCT SKU Else zp SKU End  End  declare  curr row int   0           tot rows int  0           sku nvarchar 15    null   IF OBJECT ID  tempdb    TSku    U   IS NOT NULL BEGIN     DROP TABLE   TSku END Create Table   TSku  tid int identity 1 1   relsku nvarchar 15     Select  curr row    Select MIN RowId  From   RelPro   Select  tot rows    Select MAX RowId  From   RelPro    while  curr row  lt    tot rows Begin     select  sku   SKU from   RelPro where RowID    curr row       truncate table   TSku       Insert   TSku relsku      Select distinct top 10  tzp SKU From Product tzp nolock  INNER JOIN       INTRANET  raf FocusAssociatedItem assoc nolock  ON assoc associatedItemID   tzp SKU     Where  assoc isActive 1  And  tzp ActiveInd   1  AND  assoc productID    sku       declare  curr row1 int    Select Min tid  From   TSku                tot rows1 int    Select Max tid  From   TSku        If  tot rows1  lt  gt  0      Begin         While  curr row1  lt    tot rows1         Begin             declare  col name nvarchar 15    null                       sqlstat nvarchar 500    null              set  col name     Assoc Item     Convert nvarchar 2    curr row1               set  sqlstat    update   RelPro set      col name        Select relsku From   TSku Where tid       Convert nvarchar 2    curr row1       Where RowID       Convert nvarchar 2    curr row               Exec  sqlstat               set  curr row1    curr row1   1          End     End     set  curr row    curr row   1  End  Select   From   RelPro    END GO

User · Answer

You can t do this because the table variables are out of scope     You would have to declare the table variable inside the dynamic SQL statement or create temporary tables   I would suggest you read this excellent article on dynamic SQL   http   www sommarskog se dynamic sql html

[sql] How to use table variable in a dynamic sql statement?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to dynamic-sql

Examples related to table-variable