sqlsql-serversql-server-2012table-valued-parametersdynamicquery

How to use Table -Valued Parameter with Dynamic query


I am using Table - Valued Parameter for to build dyNamic Query Using the Following code

AlTER PROCEDURE [dbo].[ABC]  
     @tblName Varchar(1000), 
     @Details ABC_TYPE Readonly


AS 

   BEGIN

   Declare @PK as nvarchar(1000)
   Declare @SyncFlag as nvarchar(1) ='S'
   Declare @SelectCommand as nvarchar(1200)
   Declare @tblName2 as nvarchar(1000) ='@Details_N'    


  Set @PK = 'PK'


  Declare @Details_N as table (Pk int)
  Insert into @Details_N(Pk) 
  select PK from @Details

  set @SelectCommand =   'Update A ' + ' set A.Sync_Flag ='''+ @SyncFlag + ''' From '+ @tblName + ' A, '  + @tblName2 + ' B ' +
  ' where A.' + @PK +'='+  'B.PK' 

  EXEC sp_executesql @SelectCommand;

This giving me error

Must declare the table variable "@Details_N"

Not finding where my I am doing wrong


Solution

  • Inside dynamic query, you cannot use table variables declared outside. Use temp table instead. Also you have complicated it little too much, here is a cleaner version

    DECLARE @SyncFlag AS NVARCHAR(1) ='S'
    DECLARE @SelectCommand AS NVARCHAR(1200)
    
    CREATE TABLE #Details_N(Pk INT)
    
    INSERT INTO #Details_N(Pk)
    SELECT PK
    FROM   @Details
    
    SET @SelectCommand = 'Update A ' + ' set A.Sync_Flag = @SyncFlag 
                            From '+ Quotename(@tblName) + ' A 
                            inner join #Details_N B '+ 'on A.PK =' + 'B.PK'
    
    EXEC Sp_executesql
      @SelectCommand,
      N'@SyncFlag NVARCHAR(1)',
      @SyncFlag 
    

    Start using INNER JOIN syntax, old style comma separated join is deprecated