September 25, 2007 at 4:47 am
Hi All,
For various reason I am inserting the update statement in a variable, then I am also putting my WHERE criteria in another variable. Finally I want to run this two variables as if its a update statement with a WHERE clause. But I am getting erorr.
Please run the following code to see.
EXAMPLE1:
Use Northwind
select * into #OrderDetails from [Order Details]
DECLARE @WhereCriteria as nvarchar(1000),@sql as nvarchar(1000)
set @WhereCriteria='price<6'
set @sql='update #OrderDetails set quantity=0 where '+@WhereCriteria
print @WhereCriteria
print @sql
exec @sql
drop table #OrderDetails
The erorr says Could not find stored procedure 'update #OrderDetails set quantity=0 where price<6'
Any idea why I am getting this erorr?
In a similar way I want to run the following script.
EXAMPLE2:
CREATE TABLE #PromoMeasure
(ID int IDENTITY(1,1),PromotionType nvarchar(100), AdType nvarchar(100), DisplayType nvarchar(100),WhereCriteria nvarchar(1000))
GO
INSERT INTO #PromoMeasure(PromotionType, AdType, DisplayType,WhereCriteria)
VALUES('BOGOF','OVERHEAD BANNER','AISLE END',
'CoulmnA>250 and CoulmnB<=-49 and CoulmnB>=-52')
GO
INSERT INTO #PromoMeasure(PromotionType, AdType, DisplayType,WhereCriteria)
VALUES('Price Promotion','SHELF TALKER','PROMOTION BIN',
'CoulmnA>250 and CoumnB<=-30 and CoumnB>=-48')
GO
DECLARE @i as INT,
@Counter AS INT,
@ID as INT,
@PromotionType nvarchar(100),
@AdType nvarchar(100),
@DisplayType nvarchar(100),
@WhereCriteria nvarchar(1000),
@sql as nvarchar(1000)
set @i=1
set @Counter=2
While @i<=@Counter
begin
print @i
print @counter
select @ID=ID,@PromotionType=PromotionType,@AdType=AdType,@DisplayType=DisplayType, @WhereCriteria=WhereCriteria
from #PromoMeasure
where ID=@i
set @sql = 'update #Table2 Set PromotionType=@PromotionType,AdType=@AdType,DisplayType=@DisplayType where'+' '+ @WhereCriteria --where [NewUnitsContribution%]>250 and DiffWithRegularPrice<=-49 and DiffWithRegularPrice>=-52
exec @sql
end
Here it gives the following erorr:
The name 'update #Table2 Set PromotionType=@PromotionType,AdType=@AdType,DisplayType=@DisplayType where CoulmnA>250 and CoulmnB<=-49 and CoulmnB>=-52' is not a valid identifier.
Can someone please help?
Thanks a million in advance.
Zee
September 25, 2007 at 5:04 am
Hi There,
Looks like the following table
#OrderDetails
was not created with in your dynamic string, putting it out of scope of your execution string.
You could change this to a perm table or a ## table so that you can get it from the scope of the string execution.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 25, 2007 at 5:09 am
No. Actually I was missing brackets that is exec(@sql) .
I made the changes and it worked for the first example.
But for example2 its giving the following erorr:
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@PromotionType'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@PromotionType'.
Although I have declared this '@PromotionType' variable, anny idea why its giving this error?
Thanks for all your help.
Zee
September 25, 2007 at 5:14 am
The variable is declared out side of the string.
Try this
set
@sql = 'update #Table2 Set romotionType= ' + @PromotionType + '.........
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 25, 2007 at 5:17 am
Well, it will be the scope now :-). In example 1, you use:
set @sql='update #OrderDetails set quantity=0 where '+@WhereCriteria
exec @sql
That means, value of @wherecriteria is resolved while the @sql variable is being composed... and @sql contains the value, not the variable.
In example 2, you are passing the variable as variable - but it is not declared inside your dynamic SQL. You need to resolve the value outside dynamic SQL, where the variable is declared - same as you did in example 1.
Chris was quicker than me :-).
BTW, Zee, you should probably spellcheck your code... it contains "romotion" and "coulmn". If this is how the columns are named, consider renaming them, this can cause confusion...
September 25, 2007 at 10:16 am
As with any dynamic SQL solution, I always highly recommend that you print out the string to execute long before you actually execute it. You can take the printed version, and execute it as a test, which will greatly help with debugging.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply