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

Question

Hi all,

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

Answer 2

In Oracle you can use TYPES and OBJECTS to achieve this IIRC. Could you not use a CURSOR/LOOP though? If the reason you're doing this is to capture dynamic data?

   CURSOR c_my_cursor IS
      SELECT * 
      FROM my_table;

BEGIN

   FOR x IN c_my_c LOOP
       IF x.employeeID IS NULL THEN
          .....
       END IF;
   END LOOP;
END;

Answer 3

If you are not on SQL Server 2008 (where you can use table-valued parameters), then you can get some other ideas for sharing data between stored procedures from Erland's great article:

http://www.sommarskog.se/share%5Fdata.html

Erland talks about table-valued parameters as well, and goes into great depth explaining why the current implementation (read only) is not quite good enough:

http://www.sommarskog.se/tableparam.html