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

---

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