存储过程：从PHP传递的变量改变了查询字符串的后半部分

Question

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

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.