0

I'm trying to convert regular query building to stored procedures. I do not know the best way to go about this, with "building" a query. Not sure how to explain, so i will give an example. My example is in PHP, but the concept is the same in any language.. example should be simple enough.

if($somevar){
    $w .= " AND SomeVar = '$somevar' ";
}
if($anothervar){
    $w .= " AND AnotherVar = '$anothervar' ";
}

$sql = "SELECT * FROM MyTable WHERE Title = 'test' " . $w;

So basically if both of those have a value, then the SQL would be:

SELECT * FROM MyTable WHERE Title = 'test' AND SomeVar = 'blah' AND 
AnotherVar = 'blah'

Because there's four possible variations of this query, what would be the best way to "build" this query using stored procedures?

Heres the other three possibilities:

SELECT * FROM MyTable WHERE Title = 'test' AND AnotherVar = 'blah'
SELECT * FROM MyTable WHERE Title = 'test' AND SomeVar = 'blah'
SELECT * FROM MyTable WHERE Title = 'test'

Do I pass both variables via BIND to a SP, then in the SP do the IF/ELSE statements.

If so, could someone provide me with an example on how to do this in the SP?

Or, is there some other way of handling this?

Thanks!

Edit: The MySQL will be converted to MSSQL from regular queries, to stored procedures

Edit 2:

based on Joe Stefanelli's comment, i think this is the answer to my question, any thoughts?

CREATE PROCEDURE testSP
    @somevar varchar(50),
    @anothervar varchar(50)
AS
    SELECT * FROM MyTable WHERE Title = @title
    AND ((SomeVar = @somevar AND @somevar IS NOT NULL) OR (@somevar IS NULL))
    AND ((AnotherVar = @anothervar AND @anothervar IS NOT NULL) OR (@anothervar IS NULL))
tcarter2005
  • 597
  • 3
  • 12
  • 19
  • 4
    For SQL Server, see Gail Shaw's blog post on [Catch-all queries](http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/) – Joe Stefanelli Jun 29 '11 at 19:06
  • the problem with the (IS NOT NULL) OR (IS NULL) is that it is killer on performance. – Al W Jun 29 '11 at 20:02

1 Answers1

0

MySQL does not support dynamic SQL in stored procedures. If you can get by with a prepared statement, you'd be good to go, but otherwise you may want to split your logic into separate procedures.

Also take a look at this SO question.

Community
  • 1
  • 1
Ryan
  • 26,884
  • 9
  • 56
  • 83