Can I pass a table as a parameter when running a stored program?

Question

Is it possible to pass a table (or a table variable) as a parameter of a stored program when executing a stored program. If so, how. I need an example.

Please, help.

Answer 1

In sql server 2005, No.

You can use xmldocs or a comma-separated string (using the 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

Oracle TYPES OBJECTS IIRC. CURSOR/LOOP? , , ?

   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

SQL Server 2008 ( ), :

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

, , ( ) :

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