Stored Procedure: Variable passed from PHP alters second half of query string

Question

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

Answer 1

You'll need to do dynamic SQL in the stored proc. This link will help you out: http://www.sommarskog.se/dynamic_sql.html

Answer 2

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?

In SQL Server, the CASE expression is not for control-of-flow like IF/ELSE.

Use:

DECLARE @SQL NVARCHAR(max)
    SET @SQL = N'SELECT ...'

    SET @SQL = @SQL + CASE sType
                        WHEN 'A' THEN ' WHERE ... '
                        WHEN 'B' THEN ' WHERE ... '
                        WHEN 'C' THEN ' WHERE ... '
                        ELSE ' '
                      END
BEGIN

  EXEC sp_executesql @SQL

END

Lest we forget the ominous tale of Little Bobby Tables, best to read The curse and blessings of Dynamic SQL.