-
SQL Returning Declared Variables
Good Morning
I am using the following sql statement to build a query that contains a string
Vars I pass in
@objectid int,
@usertype as integer,
@purchasetype as integer,
@couponcode as varchar(50)
declare @coupondiscount as decimal(3,2)
set @q2 = 'select @coupondiscount = discount from promotions as p
inner join objectreportgroups as org
on p.reportgroupid = org.reportgroupid
inner join promotioncouponcodes as pcc
on p.promotionid = pcc.promotionid
inner join tlkpCouponCodes as tcc
on tcc.couponcodeid = pcc.couponcodeid
where objectid = ' + cast(@objectid as varchar(5)) + ' and discountbyusertypeid in (0,'+cast(@usertype as varchar(5))+')
and discountbypurchasetypeid in (0,'+ cast(@purchasetype as varchar(5))+')
and p.isactive = 1 and tcc.isactive = 1
and tcc.code = '''+@couponcode +''' and iscouponrequired = 1 order by discount'
When I print @q2 I receive the following which looks to be correct and executes fine in the Query Analyser
select @coupondiscount = discount from promotions as p
inner join objectreportgroups as org
on p.reportgroupid = org.reportgroupid
inner join promotioncouponcodes as pcc
on p.promotionid = pcc.promotionid
inner join tlkpCouponCodes as tcc
on tcc.couponcodeid = pcc.couponcodeid
where objectid = 177 and discountbyusertypeid in (0,0)
and discountbypurchasetypeid in (0,0)
and p.isactive = 1 and tcc.isactive = 1
and tcc.code = 'test' and iscouponrequired = 1 order by discount
However when I try and execute @q2, it errors out
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@coupondiscount'.
Even althought I already have it declared. Any help would be greatly appreciated.
-
When you use EXEC to execute a query it is executed in a separate context than the connection that built the @q2, that's why variable declaration for @coupondiscount is not visible inside EXEC.
You will have to declare a temp table and use
insert into temptable exec(@q2)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|