creating a sp with a select for multiple combinations

[newdevr]

I am trying to create a stored procedure, passing 4 parameters (@name, @account_type, @city and @state) from an asp page. I need to somehow dynamically create the select statement, which will vary, based on values passed for each specific query, as my users may want to search by 1 or 2, but not all 4 parameters. One user may be looking for all Smiths in AR, for example. Another may want all corporate account_type records in Saint Louis. The other fields would be left blank. I'm new at SQL, and would appreciate any suggestions. Thanks.

 

[rac2]

I use indicator variables for this situation. For each one of the search criteria I define a variable that indicates whether the criteria should be used to select rows. The indicator variables can be assigned a value in my application and passed as additional parameters to my stored procedure; or sometimes there is a value for a criterion that can be used to mean the criterion is not used and this can be tested inside the stored procedure and the indicator variable can be set accordingly. Either way the fact that a criterion is not used is determined in the application and passed to the stored procedure, that is, you don't want to have undefined values for the parameters.

That is the idea, here is an example of both methods.

For account type I will pass a parameter indicating whether this field is used in the query; for city I will use the value "" to mean the field is not used.

CREATE PROCEDURE [find_them] (
   @accountType CHAR(10),
   @anyAccountType BIT,
   @city VARCHAR(50)
)
DECLARE @anyCity BIT
IF @city = "" 
BEGIN
   @anyCity = 1
END

SELECT * FROM customers
WHERE (@anyAccountType = 1 OR account_type = @accountType)
  AND (@anyCity = 1 OR city = @city)

In the application the call to the stored procedure would be like this

var accountType = "All";
var anyAccountType = 1;
var city = "";

rsGet.CommandText = "EXECUTE find_them " + accountType + ", " + anyAccountType + ", " + city;

On the SQL side you always execute the same query. When the indicator variable is true the field is irrelevant; when it is false then the criterion value deteremines the rows to be retrieved.

 

[SQLSister]

What we do is create the where statement by building it dynamically in the procedure based on the values of the parameters. Then we concatenate the where clause with the select part of the statement and execute. This gives the flexibility to do more complex types of where clauses. I have one search with 29 possible input variables (soon to be 30 , oh joy!) and depending on what the variable is I might be creating a simple where or a between or a contains and some paramters in combination use the OR operator ands ome use the and operator. So I can search for the public airports in colorado which are at an elevation between 100 and 3000 feet which have either flight schools or aircraft rentals. I suspect this would be impossible with the approach given above. Some snippets of code to give you the idea (the whole thing is way too long to post here):

If @LandMilitary = 'True'
Begin
Set @AirUseINOwn = ' OwnershipType IN("MA","MN","MR&quot'
If @SQLWhere <> ''
Set @SQLWhere = @SQLWhere + ' and ' +@AirUseINOwn
else
Set @SQLWhere = @AirUseINOwn
End


If @AirState <>''
begin

If @SQLWhere <> ''
Set @SQLWhere =@SQLWhere+ ' and State = &quot;' +@AirState+'&quot;'
else
Set @SQLWhere = 'State = &quot;' +@AirState+'&quot;'
end


If @SQLWhere <> ''
Set @SQLWhere = 'Where ' + @SQLWhere

Set QUOTED_IDENTIFIER OFF
exec(&quot;Select AirportName, AirportID,City , State, AirportType, FacilityUse, Country from #Result &quot; +@SQLWhere)

 

[rac2]

So long as you can identify a set of conditions as either used or ignored then you can always write

WHERE
( @anyValue = OKValue OR
( any conditions depending on any combination of any number of variables ) )

If the criterion is used then set the indicator variable to a value which makes the first part false and the complex criterion will determine what is selected.

Multiple criteria can be combined with OR just as they can be combined with AND.

WHERE
( @anyValueA = OKValueA OR
( any conditions depending on any combination of any number of variables ) )
OR
( @anyValueB = OKValueB OR
( any conditions depending on any combination of any number of variables ) )



You say you have 30 optional variables for your query. What a wonderful challenge! Solving that puzzle would be great fun. Unless one is distracted by an urgent DBA crisis.