pass a variable type table to a store procedure

[oigo]

Hello, How can I pass a variable type table to a store procedure in SQL server?

thanks

 

[balajees]

Hi,
There is no such variable type table.You can use a varchar type to pass its name and perform the required the operation.

Balajees

 

[tlbroadbent]

balajees,

Variable data-ypes were introduced in SQL 2000.

Oigo,

You cannot pass a table variable to a stored procedure. SQL BOL states, "All data types, except the table data type, can be used as a parameter for a stored procedure." Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[michaelkrauklis]

Ok, so you can't send a variable of type TABLE to a stored procedure, but is there any way to send a result set to a stored procedure? This would be very useful for my median calculations. MYenigmaSELF:-9
myenigmaself@myenigmaself.gaiden.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[tlbroadbent]

Do you want to return a result set to a an SP or from an SP?

If an SP returns a result set you can insert the result set into a temporary table.

set count on
Create table #tmp (<columns specifications>)

Insert #tmp
Exec yourprocname Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[michaelkrauklis]

I wanted to pass a result set TO a stored procedure. I've since found that this is impossible. Thanks anyway. MYenigmaSELF:-9
myenigmaself@myenigmaself.gaiden.com

http://myenigmaself.gaiden.com

&quot;If debugging is the process of removing bugs, then programming must be the process of putting them in.&quot; --Dykstra

 

[tlbroadbent]

You can create a temporary table in SP1, insert rows into the temp table, execute SP2 and have it process the rows in the temporary table. The scope of a temporary table is not limited to the procedure but rather to the session. Is this what you are seeking?

Example:

Create Procedure spTestReadTempTable
As

Set nocount on
Select * From #tmp
Go

Create Procedure spTestCreateTempTable
As

Set nocount on

Create table #tmp (ID int identity, ColX varchar(6))

Insert #tmp (ColX) values ('abc')
Insert #tmp (ColX) values ('def')
Insert #tmp (ColX) values ('ghi')

Exec spTestReadTempTable

Drop table #tmp

Go

exec spTestCreateTempTable
Go

drop procedure spTestReadTempTable
Go
drop procedure spTestCreateTempTable
Go
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.