Results 1 to 3 of 3

Thread: Dynamic sql with @sql

  1. #1
    Join Date
    Nov 2002
    Posts
    261

    Dynamic sql with @sql

    I have a where clause which I dynamically build that looks like this.

    SET @orderstatus = ' (t.fieldvalue in ( ''PEND'', ''REDR'') or (t.fieldvalue=''NOTA'' and pr.isactive = 0)
    or ((t.fieldvalue=''RXAP'' and m.formularymed = 0))
    or (t.fieldvalue=''NOTA'' and pr.deano = @deano)) '

    I then do @tsql = 'select * from table where ' + @orderstatus

    The problem is I get the literal value @deano not the number that is stored in @deano

  2. #2
    Join Date
    Feb 2003
    Posts
    1,048
    That's because you've enclised @deano inside single quotes. You need to concat @deano in the appropriate place

    SET @orderstatus = ' (t.fieldvalue in ( ''PEND'', ''REDR'') or (t.fieldvalue=''NOTA'' and pr.isactive = 0)
    or ((t.fieldvalue=''RXAP'' and m.formularymed = 0))
    or (t.fieldvalue=''NOTA'' and pr.deano = ' + Cast(@deano as varchar) + ')) '

  3. #3
    Join Date
    Nov 2002
    Posts
    261
    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •