T-SQL: Concept similar to C# params

Question

Does T-SQL allow a variable number of arguments to a stored procedure like params in C#?

EDIT: I'm using SQL Server 2005. That 2008 answer makes me wish we were using it...

Answer 1

No, not for things like UDFs or stored procedures. That's what tables are for. Put the values in a table somewhere (with a common key) and pass the correct key to your procedure.

Answer 2

In SQL 2008 there's Table-Valued Parameters (TVPs)

Your stored proc can accept lists of parameters..

Finally we're able to do a IN clause without relying on XML!

Mike

Answer 3

Another approach I've seen to passing in params or arrays is to pass in an XML string, dump that to a temporary table/table variable and work with it from that point. Not the easiest when you want to manually run a stored procedure, but it works as a work around to the lack of array/dynamic param support.

Answer 4

Typically

CREATE PROCEDURE dbo.sptest 
( @xml TEXT )
AS 
BEGIN
DECLARE @flag1 INT
DECLARE @flag2 VARCHAR(50)
DECLARE @flag3 DATETIME

DECLARE @idoc INT
exec sp_xml_preparedocument @idoc OUTPUT, @xml

SELECT @flag1 = firstparam, flag2 = secondparam, flag3 = thirdparam
FROM OPENXML(@idoc, '/root', 2) WITH
( firstparam INT, secondparam VARCHAR(50), thirdparam DATETIME) as x

END


exec sptest '<root><firstparam>5</firstparam><secondparam>Joes Bar</secondparam><thirdparam>12/30/2010</thirdparam></root>'

Extend as necessary

Answer 5

I've used a little function to separate a CSV string into a table

That way I could go

SELECT col1, col2
FROM myTable
WHERE myTable.ID IN (SELECT ID FROM dbo.SplitIDs('1,2,3,4,5...'))

My function is below:

CREATE FUNCTION [dbo].[SplitIDs]
(
    @IDList varchar(500)
)
RETURNS 
@ParsedList table
(
    ID int
)
AS
BEGIN
    DECLARE @ID varchar(10), @Pos int

    SET @IDList = LTRIM(RTRIM(@IDList))+ ','
    SET @Pos = CHARINDEX(',', @IDList, 1)

    IF REPLACE(@IDList, ',', '') <> ''
    BEGIN
     WHILE @Pos > 0
     BEGIN
      SET @ID = LTRIM(RTRIM(LEFT(@IDList, @Pos - 1)))
      IF @ID <> ''
      BEGIN
       INSERT INTO @ParsedList (ID) 
       VALUES (CAST(@ID AS int)) --Use Appropriate conversion
      END
      SET @IDList = RIGHT(@IDList, LEN(@IDList) - @Pos)
      SET @Pos = CHARINDEX(',', @IDList, 1)

     END
    END 
    RETURN
END

I'm sure there are better ways to implement this, this is one way I found online and it works well for what I'm doing. If there are some improvement that can be made please comment.