SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 table output
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 11/27/2013 :  11:26:50  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/27/2013 :  11:43:51  Show Profile  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 11/27/2013 :  11:48:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/27/2013 :  12:18:17  Show Profile  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 11/27/2013 :  12:35:05  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/27/2013 :  13:02:47  Show Profile  Reply with Quote
So is @TableType internal table variable used in SP?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 11/28/2013 :  04:33:21  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 11/28/2013 :  06:13:04  Show Profile  Reply with Quote
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
Time is always against us


Edited by - khtan on 11/28/2013 06:13:28
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 11/28/2013 :  06:16:37  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/28/2013 :  06:23:10  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000