Table-values function that returns result of execution stored proc. How to?  
Author Message
CMG





PostPosted: Thu Jun 07 06:21:32 CDT 2007 Top

SQL Server Developer >> Table-values function that returns result of execution stored proc. How to?

Welcome.
This is my first post in this group. I have a problem. I tried to
create a table-valued function that returns
a table as a result of execution stored procedure. Procedure
dbo.uspGetEmployeeManagers is available in AdwentureWorks database. I
have created such thing linke below, but it doesn't work. Can anyone
help me?

PS: Sorry for my english. I still learn it.

CREATE FUNCTION dbo.GetStoredProcedureResult
(
@employeeID int
)

recursion int,
empID int,
name varchar(256),
surname varchar(256),
managerID int,
managerName varchar(256),
managerSurname varchar(256)
)
BEGIN

managerName,managerSurname)
VALUES(EXEC dbo.uspGetEmployeeManagers 1)
RETURN
END
GO

SQL Server36  
 
 
Uri





PostPosted: Thu Jun 07 06:21:32 CDT 2007 Top

SQL Server Developer >> Table-values function that returns result of execution stored proc. How to? cox
As I remember you cannot use EXEC() in UDF.






> Welcome.
> This is my first post in this group. I have a problem. I tried to
> create a table-valued function that returns
> a table as a result of execution stored procedure. Procedure
> dbo.uspGetEmployeeManagers is available in AdwentureWorks database. I
> have created such thing linke below, but it doesn't work. Can anyone
> help me?
>
> PS: Sorry for my english. I still learn it.
>
> CREATE FUNCTION dbo.GetStoredProcedureResult
> (

> )

> recursion int,
> empID int,
> name varchar(256),
> surname varchar(256),
> managerID int,
> managerName varchar(256),
> managerSurname varchar(256)
> )
> BEGIN

> managerName,managerSurname)
> VALUES(EXEC dbo.uspGetEmployeeManagers 1)
> RETURN
> END
> GO
>


 
 
cox





PostPosted: Thu Jun 07 09:32:54 CDT 2007 Top

SQL Server Developer >> Table-values function that returns result of execution stored proc. How to? All right so how can I get result of stored proc in my function? Is
there any way to do that?


 
 
Erland





PostPosted: Thu Jun 07 17:31:48 CDT 2007 Top

SQL Server Developer >> Table-values function that returns result of execution stored proc. How to?
> All right so how can I get result of stored proc in my function? Is
> there any way to do that?

You cannot call a stored procedure from a function. I don't know
exactly what you want to achieve, but this article may give you some
ideas: http://www.sommarskog.se/share_data.html


--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx