How to Pass table valued function values to stored procedure

Question

How to Pass table valued function values to stored procedure

abdulrubsadi

SSC Journeyman

Points: 76
More actions
October 15, 2007 at 11:35 am

#179091

Hi All there,
Let me explain you the scene...
I have one Table-Valued function dbo.fn_GetChild which returns 4 columns as a table.
I m writing a stored procedure sp_GetMaster where in I m selecting another 4 columns i.e. a1,a2,a3 & a4 from Table1 & Table2 respectively.
Now, since my function dbo.fn_GetChild returns a table with 4 columns i.e. c1,c2,c3 & c4 respectively. I want these 4 columns to be added with the selected columns in my stored procedure sp_GetMaster. Hence, the result the should display as follows:
If the Result from function dbo.fn_GetChild is :
-----------------------------------------------------
c1 | c2 | c3 | c4
-----------------------------------------------------
then Result from stored procedure should be:
-----------------------------------------------------
a1 | a2 | a3 | a4 | c1 | c2 | c3 | c4 |
-----------------------------------------------------
now... how do i do this.... using my table-valued function columns in my stored procedure as additional columns...
Anyone's help will be highly appreciate.
Thanks in advance....

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

a procedure cannot take a table variable in as a parameter, so you'll need to tweak the logic and have the procedure CALL the function from within the code instead; it's very straighforward:

ie

create procedure (whatever @x int)

as

begin

create table #tmp(a int,b varchar(500),[c...])

insert into #tmp(a,b)

select * from dbo.yourfunction(@x)

[do more stuff]

update tmp set c=,?d=? ....

select * from tmp where [manipulations are included]

end

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 2

It won't help you now, but SQL 2008 implements the possibility to pass a table paramter to a stored procedure.

[font="Verdana"]Markus Bohse[/font]

abdulrubsadi SSC Journeyman Points: 76 More actions · Answer 3

hmm.... i knew the tmp case... but i wanted to ignore this way.... newayz... lowell.... i really appreciate your reply and thanks for replying...

abdulrubsadi SSC Journeyman Points: 76 More actions · Answer 4

thanks to you as well MarkasB..... really appreciate your replies.....

Lowell SSC Guru Points: 323547 More actions · Answer 5

you can use the table value function as a joined table or as an in() statment as well, if it's not obvious: i used a #tmp table,b ecause you might need to manipulate the data returned

select something,* from TABLEA

Inner join (SELECT * from dbo.yourfunction(@PARAM)) x on TABLEA.ID = X.ID

or

SELECT * FROM TABLEA WHERE ID IN(SELECT ID from dbo.yourfunction(@PARAM)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

noeld SSC Guru Points: 96590 More actions · Answer 6

you can also pass the parameters of the "function" to the stored procedure and use

CROSS APPLY operator to get the desired results

* Noel

abdulrubsadi SSC Journeyman Points: 76 More actions · Answer 7

noeld (10/16/2007)
you can also pass the parameters of the "function" to the stored procedure and use
CROSS APPLY operator to get the desired results

Solved !!!

Wonderful.... Noel.... U rock man....... it really worked like a charm.... i didnt expect that it would be so simply.... thanks a bunch buddy.... 🙂

I really appreciate your reply and thank you for the same.......

In addition, I sincerely thank and appreciate every member who has replied my post... keep up the good work guys..... thanks again.....

For those whos wish to see an example... please below...

SELECT A.InvoiceDt,

A.InvoiceNo,

A.PatientId,

B.ProcedureID,

Temp.*

FromInvoiceMain as A, InvoiceDetail as B

CROSS APPLY

MyDB.dbo.fn_GetProcedureInfo(@p1,@p2,B.ProcedureID) as Temp

WHERE A.SetupId=B.SetupId AND

A.ProjectId=B.ProjectId AND

A.InvoiceNo=B.InvoiceNo

Result:

-------

Result from Table-Valued Function dbo.fn_GetProcedureInfo(@p1,@p2,B.ProcedureID) :

----------------------------------------------------------

ProcedureID | PName | PGroup |

----------------------------------------------------------

Final Query Result.. so called desired result:

---------------------------------------------------------------

---------------------------------------------------------------

[/code]