Stored Procedure: Variable passed from PHP alters second half of query string
- by Stephanie
Hello everyone,
Basically, we have an ASP website right now that I'm converting to PHP. We're still using the MSSQL server for the DB -- it's not moving.
In the ASP, now, there's an include file with a giant sql query that's being executed. This include sits on a lot of pages and this is a simple version of what happens. Pages A, B and C all use this include file to return a listing.
In ASP, Page A passes through variable A to the include file - page B passes through variable B -- page C passes through variable C, and so on.
The include file builds the SQL query like this:
sql = "SELECT * from table_one LEFT OUTER JOIN table_two ON table_one.id = table_two.id"
then adds (remember, ASP), based on the variable passed through from the parent page,
Select Case sType
Case "A"
sql = sql & "WHERE LOWER(column_a) <> 'no' AND LTRIM(ISNULL(column_b),'') <> '' ORDER BY column_a
Case "B"
sql = sql & "WHERE LOWER(column_c) <> 'no' ORDER BY lastname, firstname
Case "C"
sql = sql & "WHERE LOWER(column_f) <> 'no' OR LOWER(column_g) <> 'no' ORDER BY column_g
As you notice, every string that's added on as the second part of the sql query is different than the previous; not just one variable can be substituted out, which is what has me stumped.
How do I translate this case / switch into the stored procedure, based on the varchar input that I pass to the stored procedure via PHP?
This stored procedure will actually handle a query listing for about 20 pages, so it's a hefty one and this is my first major complicated one. I'm getting there, though! I'm also just more used to MySQL, too. Not that they're that different. :P
Thank you very much for your help in advance.
Stephanie
