The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.:

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.

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?