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

Is it possible to pass a table (or table variable) as a parameter of a storedproc when executing the sto开发者_StackOverflow中文版redproc. If yes then how. I need an example.

Please help.

---

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

---

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;

---

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_data.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