Can I pass a table as a parameter when executing a storedproc?

Question

Is it possible to pass a table (or table variable) as a parameter of a storedproc when executing the storedproc. If yes then how. I need an example.

Please help.

Answer 1

In sql server 2005, No.

You can use xmldocs, or comma delimited string (using a split function)

CREATE FUNCTION [dbo].[SplitString]
(
        @String VARCHAR(8000) ,
        @Delimiter  VARCHAR(10)
)
RETURNS @RetTable TABLE(
        String varchar(1000)
)
AS 
BEGIN
    DECLARE @i INT ,
            @j INT
    SELECT  @i = 1
    WHILE @i <= LEN(@String)
    BEGIN
        SELECT  @j = CHARINDEX(@Delimiter, @String, @i)
        IF @j = 0
        BEGIN
            SELECT  @j = LEN(@String) + 1
        END
        INSERT  @RetTable SELECT SUBSTRING(@String, @i, @j - @i)
        SELECT  @i = @j + LEN(@Delimiter)
    END
    RETURN
END

see also

passing-lists-to-sql-server-2005-with-xml-parameters

and

beginning-sql-server-2005-xml-programming