CODEWITHSUNDEEP
sql-serverdynamic-sqltemp-tables
Karl
Karl

Reputation: 41

Temp table value in where clause of Dynamic SQL

Need to know whether below Syntax is correct while using a value from temp table in where clause of dynamic SQL

DECLARE @sqlQ nvarchar(1000);

if OBJECT_ID('tempdb..#Tem') is not null BEGIN DROP tABLE #Tem END
create table #Tem
(order nvarchar(10))

insert into #Tem
Select orderID from customerdetails where  OrderID >100


  SET @sqlQ = N'UPDATE FINALTable SET Highvalcusomer=1 where 
   orderno=#Tem.order'
      EXEC @sqlQ

Upvotes: 0

Views: 71

Answers (1)

gbn
gbn

Reputation: 432712

Just do it in one go

   UPDATE 
    FINALTable 
   SET 
    Highvalcusomer=1 
   where 
     orderno IN (Select orderID from customerdetails where  OrderID >100)

Or if you really want to use dynamic SQL

SET @sqlQ = N'UPDATE FINALTable SET Highvalcusomer=1 where 
            orderno IN (Select orderID from #Tem)'
EXEC (@sqlQ )

#Tem will be in scope for the dynamic SQL

Upvotes: 1

Related Questions