Creating SQL statement based on parameters

[jararaca]

Hi,

I need to build the where clause of a stored procedure that may or may not receive a number of parameters, and, if it does, I have no way of knowing in what combination those parameters will be supplied. The stored procedure is designed to allow the user to select records based on a variable combination of criteria. For example, suppose there is a table called Questionnaire, and questionnaires have fields like ID (PK), title (Unique), Month, Year, Provider, etc.

If the user supplies only a provider, then I need to create a statment like:

SELECT * FROM Questionnaire WHERE Provider = @Provider

But if the user supplies both a Provider and Month, then the statement needs to be able to use that to further narrow down the search, like this:

SELECT * FROM Questionnaire WHERE Provider=@Provider AND Month=@Month.

And so on with other parameters.

There are 10 possible parameters. The combination of possibilities would probably require me to build hundreds of stored procedures. I imagine there is a way to find out, from inside the stored procedure, which parameters are supplied. Something like this (I'm using pseudo-code, because I don't know SQL Server syntax)

Declare WhereClause as string

If @Provider is supplied then
WhereClause = "Provider = " & @Provider
End If
If @Month is supplied then
If WhereClause is not empty then
WhereClause = WhereClause & " And Month = " @Month
Else
WhereClause = "Month = " @Month
End If
End if
...

Then assmble the SQL statement:

SELECT * FROM...
WHERE & WhereClause

Can anyone please give me a clue about how to get started with putting something like this together? Sample code would be appreciated.

Thanks!

 

[r937]

the easiest way to handle this is to start your query like this --

SELECT * FROM ...
WHERE 1=1

now, all you have to do is append additional clauses for any supplied parameters, prefacing each one with AND

If @Provider is supplied then
WhereClause = WhereClause & "AND Provider = " & @Provider
End If
If @Month is supplied then
WhereClause = WhereClause & "AND Month = " & @Month
End If

and so on

this way, you don't have to worry about any previous parameters before you append another condition

r937.com | rudy.ca

 

[jararaca]

Great. Thanks!

So "WhereClause" is a reserved word? Simply by changing the value of this variable, I'm changing the Where clause of the sql statment?

Thanks, again!

 

[r937]

no, man, i was simply using the same pseudocode you were



r937.com | rudy.ca

 

[jararaca]

Oh. So my next question is what is the correct syntax? That's really the question I'm asking. I was using pseudo-code because I don't know the syntax, which is what I'm looking for!

Thanks, again.

 

[jararaca]

Don't worry! I found it. Thanks, though...