table output - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

table output

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-11-27 : 11:26:50

Hello,
Is the following possible?
Two stored procedures, sp1 and sp2
1-sp1 to output the result of a select query into a table type table i.e. @TT
2-sp2 to call sp1 and retrieve the result of @TT

?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 11:43:51

from books online
You cannot specify a table-valued parameter as the target of an INSERT EXEC statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-11-27 : 11:48:16

I do not want to use "insert exec".
Just want to use the result of that table to be retrieved from the stored proc into a table type variable.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 12:18:17

quote:
Originally posted by arkiboys

I do not want to use "insert exec".
Just want to use the result of that table to be retrieved from the stored proc into a table type variable.
Thanks

how is that table populated? using insert..exec only right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-11-27 : 12:35:05

at the end of sp1, there is insert into @TableType select * from #tblData
So I thought maybe @TableType can be retrieved within sp2 since sp2 calls sp1.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 13:02:47

So is @TableType internal table variable used in SP?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-11-28 : 04:33:21

quote:
Originally posted by visakh16

So is @TableType internal table variable used in SP?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

@TT is used to pass a table from one sp to another.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-28 : 06:13:04

refer to http://technet.microsoft.com/en-us/library/bb510489.aspx

quote:
Restrictions

Table-valued parameters have the following restrictions:

SQL Server does not maintain statistics on columns of table-valued parameters.

Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.

Which basically means you can't use it to do what you want

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-28 : 06:16:37

what you want can be done with temp table


Create temp table inside SP2

insert into #temp ( . . . )
exec sp1

... SP 2 consume the result of #temp

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-28 : 06:23:10

quote:
Originally posted by arkiboys

quote:
Originally posted by visakh16

So is @TableType internal table variable used in SP?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

@TT is used to pass a table from one sp to another.

so its a table valued parameter isnt it? Table Valued parameter should be passed as read only so you cant modify data in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources