0

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
Karl
  • 41
  • 1
  • 7
  • No, that's not right. What are you actually trying to achieve here? – Thom A Oct 12 '18 at 11:11
  • Possible duplicate of [T-SQL Dynamic SQL and Temp Tables](https://stackoverflow.com/questions/2917728/t-sql-dynamic-sql-and-temp-tables) – Alejandro Oct 12 '18 at 11:11
  • I need to compare the value from temp table and the table which I am going to update – Karl Oct 12 '18 at 11:12
  • Also need to know the above given syntax of dynamic query is correct or not since I am using #Tem.order – Karl Oct 12 '18 at 11:14

1 Answers1

1

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Is it possible to use without 'orderno IN' and just map as above in the question? – Karl Oct 12 '18 at 11:15
  • The name N'UPDATE FINALTable SET Highvalcusomer=1 where orderno IN (Select orderID from #Tem)' is not a valid identifier got this error – Karl Oct 12 '18 at 11:41