>>I'm writing stored procedures to retrieve data from various tables for a web application. My question is kind of broad in nature.
>>
>>When I want to retrieve the same data based on slightly different criteria should I write two different stored procedures, or is there a way to make it as one.
>>
>>For example, my first query I want to retrieve an equipment list based only on customer ID. In the second instance, I would want to add the equipment ID along with the customer ID.
>>
>>Should this be one stored procedure or two? Is there a way to write a stored procedure that knows how many parameters are passed to it?
>>
>>Thank you
>
>I agree with Kevin, you can build an SP with several parameters with default values. Now, if your parameter is integer and you would to ignore parameter if it's NULL and use if it's not null, here is an approach you can use
>

>select myFields from myTable
> where IntField between coalesce(@IntParameter,,( -1 * convert(int, 0x7fffffff) )-1) and coalesce(@IntParameter,convert(int, 0x7fffffff))

>
>
>See Is it worth the effort?
>
>In T-SQL SP doesn't know the number of parameters passed in, so you need to check every parameter for not being a default value.


Naomi / Kevin,

Thank you both for your answers. That put me on the right track and got me going on writing my stored procedures.

Elgin