Results 1 to 2 of 2

Thread: SQL Returning Declared Variables

  1. #1
    Join Date
    Mar 2003
    Posts
    4

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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
  •