Dynamic query help

Question

Post reply

Dynamic query help

SqlSavvy

SSCrazy

Points: 2641
More actions
April 27, 2009 at 10:46 am

#129622

Can you please help? I am dynamically trying to run the updatestaments....when I print the stament to the screen, the statments looks fine. But when I execute the command it gives me error.
My sql statment is as follows.
DECLARE mycursor CURSOR
FOR
SELECT SND_LOC_ID, RCV_LOC_ID from #temp
OPEN mycursor
FETCH NEXT FROM mycursor INTO @SND_LOC_ID, @RCV_LOC_ID
WHILE @@FETCH_STATUS >= 0
BEGIN
SET @CMD = 'UPDATE t1 SET
t1.Sat = CASE WHEN t2.Day_To_Receive = ''Saturday'' THEN 1 ELSE 0 END,
t1.Sun = CASE WHEN t2.Day_To_Receive = ''Sunday'' THEN 1 ELSE 0 END,
t1.Mon = CASE WHEN t2.Day_To_Receive = ''Monday'' THEN 1 ELSE 0 END,
t1.Tue = CASE WHEN t2.Day_To_Receive = ''Tuesday'' THEN 1 ELSE 0 END,
t1.Wed = CASE WHEN t2.Day_To_Receive = ''Wednesday'' THEN 1 ELSE 0 END,
t1.Thu = CASE WHEN t2.Day_To_Receive = ''Thursday'' THEN 1 ELSE 0 END,
t1.Fri = CASE WHEN t2.Day_To_Receive = ''Friday'' THEN 1 ELSE 0 END
FROM EDI_Days_To_Recieve t1
JOIN #temp t2 on t1.[SND_LOC_ID] = t2.[SND_LOC_ID] AND t1.[RCV_LOC_ID] = t2.[RCV_LOC_ID]'
+ ' WHERE t1.[SND_LOC_ID] '+ '=' + ''''+@SND_LOC_ID+'''' + ' AND t1.[RCV_LOC_ID]' + '=' + ''''+@RCV_LOC_ID +''''
PRINT (@CMD)
EXEC @CMD
FETCH NEXT FROM mycursor INTO @SND_LOC_ID, @RCV_LOC_ID
END
CLOSE mycursor
DEALLOCATE mycursor
When I run the print statement, it prints the statment that I wanted.
UPDATE t1 SET
t1.Sat = CASE WHEN t2.Day_To_Receive = 'Saturday' THEN 1 ELSE 0 END,
t1.Sun = CASE WHEN t2.Day_To_Receive = 'Sunday' THEN 1 ELSE 0 END,
t1.Mon = CASE WHEN t2.Day_To_Receive = 'Monday' THEN 1 ELSE 0 END,
t1.Tue = CASE WHEN t2.Day_To_Receive = 'Tuesday' THEN 1 ELSE 0 END,
t1.Wed = CASE WHEN t2.Day_To_Receive = 'Wednesday' THEN 1 ELSE 0 END,
t1.Thu = CASE WHEN t2.Day_To_Receive = 'Thursday' THEN 1 ELSE 0 END,
t1.Fri = CASE WHEN t2.Day_To_Receive = 'Friday' THEN 1 ELSE 0 END
FROM EDI_Days_To_Recieve t1
JOIN #temp t2 on t1.[SND_LOC_ID] = t2.[SND_LOC_ID] AND t1.[RCV_LOC_ID] = t2.[RCV_LOC_ID] WHERE t1.[SND_LOC_ID] ='006906614GE' AND t1.[RCV_LOC_ID]='AUXILIUMGE
But when I execute the statement I am gettting the following error..
Msg 203, Level 16, State 2, Line 44
The name 'UPDATE t1 SET
t1.Sat = CASE WHEN t2.Day_To_Receive = 'Saturday' THEN 1 ELSE 0 END,
t1.Sun = CASE WHEN t2.Day_To_Receive = 'Sunday' THEN 1 ELSE 0 END,
t1.Mon = CASE WHEN t2.Day_To_Receive = 'Monday' THEN 1 ELSE 0 END,
t1.Tue = CASE WHEN t2.Day_To_Receive = 'Tuesday' THEN 1 ELSE 0 END,
t1.Wed = CASE WHEN t2.Day_To_Receive = 'Wednesday' THEN 1 ELSE 0 END,
t1.Thu = CASE WHEN t2.Day_To_Receive = 'Thursday' THEN 1 ELSE 0 END,
t1.Fri = CASE WHEN t2.Day_To_Receive = 'Friday' THEN 1 ELSE 0 END
FROM EDI_Days_To_Recieve t1
JOIN #temp t2 on t1.[SND_LOC_ID] = t2.[SND_LOC_ID] AND t1.[RCV_LOC_ID] = t' is not a valid identifier.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You need some brackets in the exec statement.

Change

EXEC @CMD

to

EXEC (@CMD)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Also, why a cursor? Unless I've missed something, the entire thing, cursor and all can be replaces by this:

UPDATE t1 SET

t1.Sat = CASE WHEN t2.Day_To_Receive = 'Saturday' THEN 1 ELSE 0 END,

t1.Sun = CASE WHEN t2.Day_To_Receive = 'Sunday' THEN 1 ELSE 0 END,

t1.Mon = CASE WHEN t2.Day_To_Receive = 'Monday' THEN 1 ELSE 0 END,

t1.Tue = CASE WHEN t2.Day_To_Receive = 'Tuesday' THEN 1 ELSE 0 END,

t1.Wed = CASE WHEN t2.Day_To_Receive = 'Wednesday' THEN 1 ELSE 0 END,

t1.Thu = CASE WHEN t2.Day_To_Receive = 'Thursday' THEN 1 ELSE 0 END,

t1.Fri = CASE WHEN t2.Day_To_Receive = 'Friday' THEN 1 ELSE 0 END

FROM EDI_Days_To_Recieve t1

JOIN #temp t2 on t1.[SND_LOC_ID] = t2.[SND_LOC_ID] AND t1.[RCV_LOC_ID] = t2.[RCV_LOC_ID]

It doesn't need a where clause limiting the update to only rows in #temp, because there's a join to #temp already within the update and that join is on the columns used to match to the cursor values.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SqlSavvy SSCrazy Points: 2641 More actions · Answer 3

Now what is wrong with the following syntax? RowID is declared as Integer...I am getting Row using Row_Number function.

Its giving me Incorrect Syntax near ''

WHILE @@FETCH_STATUS >= 0

BEGIN

SET @CMD = 'UPDATE t1 SET

t1.Sat = CASE WHEN t2.Day_To_Receive = ''Saturday'' THEN 1 ELSE 0 END,

t1.Sun = CASE WHEN t2.Day_To_Receive = ''Sunday'' THEN 1 ELSE 0 END,

t1.Mon = CASE WHEN t2.Day_To_Receive = ''Monday'' THEN 1 ELSE 0 END,

t1.Tue = CASE WHEN t2.Day_To_Receive = ''Tuesday'' THEN 1 ELSE 0 END,

t1.Wed = CASE WHEN t2.Day_To_Receive = ''Wednesday'' THEN 1 ELSE 0 END,

t1.Thu = CASE WHEN t2.Day_To_Receive = ''Thursday'' THEN 1 ELSE 0 END,

t1.Fri = CASE WHEN t2.Day_To_Receive = ''Friday'' THEN 1 ELSE 0 END

FROM EDI_Days_To_Recieve t1

JOIN #temp t2 on t1.[SND_LOC_ID] = t2.[SND_LOC_ID] AND t1.[RCV_LOC_ID] = t2.[RCV_LOC_ID]'

+ ' WHERE t2.[Row]' + '=' + ''+@RowID''

+ ' AND t1.[SND_LOC_ID]' + '=' + ''''+@SND_LOC_ID+''''

+ ' AND t1.[RCV_LOC_ID]' + '=' + ''''+@RCV_LOC_ID +''''

PRINT (@CMD)

--EXEC (@CMD)

FETCH NEXT FROM mycursor INTO @RowID, @SND_LOC_ID, @RCV_LOC_ID