SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 To put a string in a variable & use it in a WHERE?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 09/25/2007 :  06:41:28  Show Profile  Reply with Quote
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

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 09/25/2007 :  06:47:12  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
quote:
Originally posted by zeeshan13

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





Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 09/25/2007 :  07:02:20  Show Profile  Reply with Quote
harsh_athalye,
Thanks for the quick reply.
I made the changes and it worked for the first example.
But for example 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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30178 Posts

Posted - 09/25/2007 :  07:12:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
http://www.sommarskog.se/dynamic_sql.html

set @sql = 'update #Table2 Set PromotionType=' + quotename(@PromotionType, '''') + ',AdType=' + quotename(@AdType, '''') + ',DisplayType=' + quotename(@DisplayType, '''') + ' where ' + @WhereCriteria
exec (@sql)



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 09/25/2007 :  08:55:17  Show Profile  Reply with Quote
@PromotionType is defined outside the scope of your executed SQL Statement, so you must concatenate the value as part of your statement. You cannot refer to variables inside a dynamic sql statement that were defined outside that statement.

set @sql = 'update #Table2 Set PromotionType=' + @PromotionType + ',AdType=@AdType,DisplayType=@DisplayType where'+' '+ @WhereCriteria --where [NewUnitsContribution%]>250 and DiffWithRegularPrice<=-49 and DiffWithRegularPrice>=-52
exec @sql



e4 d5 xd5 Nf6
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 09/25/2007 :  10:54:20  Show Profile  Reply with Quote
blindman / Peso,

Yes thats what i already did and it worked :)
Thanks for your help. I want to put a star, or lets say I want to accept your solution here...how do you do that?

Also I will be posting another problem, please check.

Thanks,

Zee
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000