Where clause in a variable?

• petey20

  SSCommitted

  Points: 1741

  More actions

  November 3, 2004 at 1:09 pm

  #60642

  Below I've posted code to a select statement in a SP of mine.  I'm trying to dynamically create the where clause based off of a value in the variable @JoyOrder.

   Basically this "and" part of the where clause needs executed.  When I paste this in a separate sql script and run it, it returns the correct data, but here it does not,  By the way the  @PackDate  variable is passed and is 11/02/2004 (it's a char data type).

  So what am I doing wrong in the select below?

   

  Thanks

   

  Declare @JoyClause varchar(2000)

     if @JoyOrder = 1

     begin

        set @JoyClause = '  and (ltrim(pck_hdr.cust_num) =  ''103'' OR ltrim(pck_hdr.cust_num) = ''104'' ) '

     end

  declare PckHdrCrs cursor local static for

      select  *

     from pck_hdr

     where convert(varchar,pck_hdr.pack_date,101) = @PackDate  + @JoyClause

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  November 3, 2004 at 2:05 pm

  #528846

  Have a look here

  http://www.sommarskog.se/dynamic_sql.html

  http://www.sommarskog.se/dyn-search.html

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]