You can send a multivalue parameter if you use the XML datatype. It would be really cool if you could use Oracle’s trick of sending an array as a parameter, but alas… Still, the XML datatype option is nice and flexible and really quite simple to use. Let’s say you use a very simple example like this one:

/* first I declare my variable and set its value */
DECLARE @Multi_Values xml
SET @Multi_Values ='135‘

/* then I declare my procedure to handle multiple values from one variable */
CREATE PROCEDURE MultivalueXMLParameter(@MultiVals xml)
AS
DECLARE @Multi_Values TABLE (ID int)

INSERT INTO @Multi_Values (ID)
SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @MultiVals.nodes('/MyValues/id') as ParamValues(ID)

(conditional processing goes here)

Now, I could have accomplished the same thing by using a temporary table (gasp), but I think you’ll find that this approach is both less resource-intensive, simpler, and less likely to cause a DBA to have a fit.