SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    .NET inside archigamer's Avatar
    Join Date
    Jan 2002
    Location
    Strongsville OH
    Posts
    1,534
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Stored Proc problem

    I have the following stored proc

    Code:
    CREATE PROCEDURE GetAdvancedSearch
    
    @searchKey varchar(100), 
    @model varchar(100),
    @category int,
    @availability int,
    @minPrice money  = 0.00,
    @maxPrice money = 100000.00
    
    AS
    
    declare @finalCategory varchar(20)
    declare @finalAvailability varchar(20)
    
    if @category = 0
          select @finalCategory = "IS NOT NULL"
    else
          select @finalCategory = '=' + @category
    
    if @availability = 0
           select @finalAvailability = "IS NOT NULL";
    else
           select @finalAvailability = '=' + @availability;
    
    select distinct p.product_id, pi.product_image_imagedir, p.product_title, p.product_price 
    from product p 
    right join product_link pl on p.product_id = pl.product_link_pid
    left join product_image pi on p.product_id = pi.product_image_pid
    where (((p.product_title like @searchKey) or (p.product_description like @searchKey))
    or (p.product_model = @model)
    and (pl.product_link_cid  @finalCategory ) 
    and (p.product_availability  @finalAvailability)
    and ((p.product_price >= @minPrice) and (p.product_price <= @maxPrice))
    )
    order by p.product_title, p.product_price desc;
    my problem is SQL Server is screaming at me about @finalCategory and @finalAvailability where I use it at the end of the stored proc. Says it is invalid syntax. What I am doing essentially is if the value of @availability = 0 I want the proc to select any rows in the p.product_availability else use the assigned value to filter results and the same thing for the @category. so technically what I need to do is connect the 2 strings inside the actual query.
    Web Finesse Studios
    Professional, business oriented web hosting and development.

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What are you doing with all those semicolons? SQL Server don't grok PL/SQL!

    Plus you need to use dynamic SQL in order to do tricks like IS NOT NULL vs. = 'bob', depending on the version of SQL Server you have (which you didn't list).

  3. #3
    .NET inside archigamer's Avatar
    Join Date
    Jan 2002
    Location
    Strongsville OH
    Posts
    1,534
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well I am running MSDE which is free version of SQL server 2000

    as for the extra semicolons...i guess you get the languages messed up when coing in C# and SQL lol
    Web Finesse Studios
    Professional, business oriented web hosting and development.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    to the optimizer, it might make a big difference between having a condition on a column, such as where foo is not null, and not having a condition on that column at all

    start your WHERE clause off with 1=1 and then you can use AND to add conditions as appropriate on those columns that need filtering

    see The "any" option in dynamic search

    rudy

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In order to get rid of the conditional logic you could have:
    WHERE ( p.product_availability = @product_availability OR p.product_availability IS NULL )

    That should work, provided 0 is not a value for product_availability (it'll work, of course, but you'll get spurious rows).

  6. #6
    .NET inside archigamer's Avatar
    Join Date
    Jan 2002
    Location
    Strongsville OH
    Posts
    1,534
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am trying rudy's idea. however it is complaining about the 'and' keyword in the if statements here is the code

    Code:
    CREATE PROCEDURE GetAdvancedSearch
    
    @searchKey varchar(100), 
    @model varchar(100),
    @category int,
    @availability int,
    @minPrice decimal,
    @maxPrice decimal
    
    AS
    
    select distinct p.product_id, pi.product_image_imagedir, p.product_title, p.product_price 
    from product p 
    right join product_link pl on p.product_id = pl.product_link_pid
    left join product_image pi on p.product_id = pi.product_image_pid
    where (
    ((p.product_title like @searchKey) or (p.product_description like @searchKey))
    or (p.product_model = @model)
    
    if @category != 0
    begin
          and (pl.product_link_cid = @category) 
    end
    
    if @availability != 0
    begin
         and (p.product_availability = @availability)
    end
    and ((p.product_price >= @minPrice) and (p.product_price <= @maxPrice))
    )
    order by p.product_title, p.product_price desc;
    Web Finesse Studios
    Professional, business oriented web hosting and development.

  7. #7
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can't have IF blocks in the middle of WHERE clauses. Rudy's idea assumes you are working in an application context in which you are generating SQL to be dispatched to the DBMS (like you were sitting at the SQL window and typed in SELECT * FROM ... and hit execute). It doesn't apply for stored procedures.

    Try my idea above; if that doesn't work you can always use dyamic SQL.

    Dynamic SQL requires you to create the SQL as a string then pass it to the exec() function, e.g.:
    Code:
    CREATE PROCEDURE GetAdvancedSearch
    
    @searchKey varchar(100), 
    @model varchar(100),
    @category int,
    @availability int,
    @minPrice money  = 0.00,
    @maxPrice money = 100000.00
    
    AS
    
    declare @finalCategory varchar(20)
    declare @finalAvailability varchar(20)
    
    declare @sqlStmnt VARCHAR( 3000 ) -- or however large all this text is
    
    if @category = 0
          select @finalCategory = "IS NOT NULL"
    else
          select @finalCategory = '=' + @category
    
    if @availability = 0
           select @finalAvailability = "IS NOT NULL"
    else
           select @finalAvailability = '=' + @availability
    
    select @sqlStmnt = "select distinct p.product_id, pi.product_image_imagedir, p.product_title, p.product_price 
    from product p 
    right join product_link pl on p.product_id = pl.product_link_pid
    left join product_image pi on p.product_id = pi.product_image_pid
    where (((p.product_title like " + @searchKey + ") or (p.product_description like " + @searchKey + "))
    or (p.product_model = " + @model + ")
    and (pl.product_link_cid " + @finalCategory + ") 
    and (p.product_availability " + @finalAvailability + ")
    and ((p.product_price >= " + @minPrice + ") and (p.product_price <= " + @maxPrice + "))
    )
    order by p.product_title, p.product_price desc"
    
    exec( @sqlStmnt )

  8. #8
    .NET inside archigamer's Avatar
    Join Date
    Jan 2002
    Location
    Strongsville OH
    Posts
    1,534
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It giving an error for some reason. Maybe an MSDE limitation? i don't think so though

    here is the exact error
    Error 257: The identifier that starts with 'select distinct p.product_id, pi.product_image_imagedir, p.product_title, p.product_price from product p right join product_' is to long maximum length is 128.
    Web Finesse Studios
    Professional, business oriented web hosting and development.

  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, it could be. Google doesn't find any results with that error code; do you have an error code list you can find -- if you can locate that error code there it could answer that question.

    In the worst case, you can create several stored procedures which have the where clause hard-coded, e.g.

    Code:
    CREATE PROCEDURE GetAdvancedSearch
    
    @searchKey varchar(100), 
    @model varchar(100),
    @category int,
    @availability int,
    @minPrice money  = 0.00,
    @maxPrice money = 100000.00
    
    AS
    
    declare @finalCategory varchar(20)
    declare @finalAvailability varchar(20)
    
    if @category = 0 and @availability = 0
          exec GetAdvSearchBothParams @searchKey, @model, @minPrice, @maxPrice
          -- This stored proc would have SQL which has hard coded 'IS NOT NULL'
          -- in the WHERE clause for both parameters
    else if @category = 0 and @availability <> 0
          exec GetAdvSearchAvailability @searchKey, @model, @availability, @minPrice, @maxPrice
          -- This one has IS NOT NULL for category, but product_avail = @avail...
    else if @category <> 0 and @availability = 0
          exec GetAdvSearchCategory @searchkey, @model, @category, @minPrice, @maxPrice
    else
    -- Both exist
    select distinct p.product_id, pi.product_image_imagedir, p.product_title, p.product_price 
    from product p 
    right join product_link pl on p.product_id = pl.product_link_pid
    left join product_image pi on p.product_id = pi.product_image_pid
    where (((p.product_title like @searchKey) or (p.product_description like @searchKey))
    or (p.product_model = @model)
    and (pl.product_link_cid  = @category ) 
    and (p.product_availability = @aAvailability)
    and ((p.product_price >= @minPrice) and (p.product_price <= @maxPrice))
    )
    order by p.product_title, p.product_price desc
    It's ugly, but it'd work if all else fails.

  10. #10
    .NET inside archigamer's Avatar
    Join Date
    Jan 2002
    Location
    Strongsville OH
    Posts
    1,534
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok well i got it to work had to replace the double quotes at the beginning and end of the statement with single quotes. (ya i said WTF too)

    Now to test it to see if i get desired results.
    Web Finesse Studios
    Professional, business oriented web hosting and development.

  11. #11
    .NET inside archigamer's Avatar
    Join Date
    Jan 2002
    Location
    Strongsville OH
    Posts
    1,534
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok guys I almost got this working here is a sample query (after all of the string manipulations)
    Code:
    select distinct p.product_id, pi.product_image_imagedir, p.product_title, p.product_price, pl.product_link_cid, pl.product_link_pid
    from product p 
    right join product_link pl on p.product_id = pl.product_link_pid 
    left join product_image pi on p.product_id = pi.product_image_pid 
    where (((p.product_title like "%pro%" ) 
    or (p.product_description like "%pro%" )) 
    or (p.product_model = "%%" ) 
    and (pl.product_link_cid = 5 ) 
    and (p.product_availability = 1 ) 
    and ((p.product_price >= 15.00 ) 
    and (p.product_price <= 25.00 )) ) 
    order by p.product_title asc, p.product_price desc
    my problem is it is not filtering the category correctly. notice that @category is equal to 5 (next to @pl.product_link_cid) (cid means category id) i still get results outputting products from categories 4,6,7, and 8. can anyone explain why the rows arent being filtered based on category id?
    Web Finesse Studios
    Professional, business oriented web hosting and development.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    start with your WHERE clause --
    Code:
    where (
          (
          ( p.product_title like "%pro%" )
       or ( p.product_description like "%pro%" )
          ) 
       or ( p.product_model = "%%" ) 
      and ( pl.product_link_cid = 5 ) 
      and ( p.product_availability = 1 ) 
      and (
          ( p.product_price >= 15.00 ) 
      and ( p.product_price <= 25.00 )
          )
          )
    now remove the redundant parentheses, and insert parentheses which accurately reflect how ANDs and ORs are evaluated --
    Code:
    where (
          p.product_title like "%pro%" 
       or p.product_description like "%pro%" 
          ) 
       or (
          p.product_model = "%%" 
      and pl.product_link_cid = 5 
      and p.product_availability = 1 
      and p.product_price >= 15.00 
      and p.product_price <= 25.00
          )
    substituting letter for each condition, you have --
    Code:
    where ( a or b ) 
       or ( c and d and e and f and g )
    can you see now where the results are coming from?

    rudy

  13. #13
    .NET inside archigamer's Avatar
    Join Date
    Jan 2002
    Location
    Strongsville OH
    Posts
    1,534
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok thanks it got it working all. thanks to both of you for your help
    Web Finesse Studios
    Professional, business oriented web hosting and development.


Bookmarks

Posting Permissions

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