Pass variable number of parameters to a stored proc

Is it possible to create a stored proc that allows the passing of a variable
number of parameters? I'm creating a stored proc to populate a Suggested PO
form. It may require filtering on one or more vendor IDs (string data). I've
looked through BOL but nothing is jumping out at me. I also was looking at
the string manipulation functions for parsing a single variable but it looks
like it would be ugly. TIA!Hi,
You will have to use dynamic SQL inside the procedure to parse the parameter
variable which hold one or more input values.
See the below URL for the various usage of dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
SQL Server MVP
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23RizK7jxGHA.480@.TK2MSFTNGP06.phx.gbl...
> Is it possible to create a stored proc that allows the passing of a
> variable
> number of parameters? I'm creating a stored proc to populate a Suggested
> PO
> form. It may require filtering on one or more vendor IDs (string data).
> I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it
> looks
> like it would be ugly. TIA!
>
>|||Ron Hinds wrote:
> Is it possible to create a stored proc that allows the passing of a variable
> number of parameters? I'm creating a stored proc to populate a Suggested PO
> form. It may require filtering on one or more vendor IDs (string data). I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it looks
> like it would be ugly. TIA!
>
>
Here is one approach:
CREATE PROCEDURE MyProc
@.Var1 INT = NULL,
@.Var2 INT = NULL
AS
SELECT Field1, Field2
FROM Table
WHERE
((@.Var1 IS NULL) OR (Field3 = @.Var1))
AND
((@.Var2 IS NULL) OR (Field4 = @.Var2))
The optimizer is smart enough to realize that if the left side of the OR
is true, there is no need to evaluate the right side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com