Passing table-type variables to SP?

Sorry about posting twice, but I had some trouble with setting the date on
this machine... :)
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine... :)
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine... :)
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>