Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

exec stored proc inside a function Expand / Collapse
Author
Message
Posted Friday, September 18, 2009 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 18, 2009 8:53 AM
Points: 10, Visits: 61
Aim:
a procedure returns 30 column results.
i only want 3 of them and dont want to create a temp table with a 2nd select on it. (insert into #T exec myproc)
plan was to create a function that executes this proc and selects the columns i want.
in reference to the msdn site i found that it is possible but i do get an error on compilation.
"Incorrect syntax near the keyword 'EXECUTE'."


==================================================
http://msdn.microsoft.com/en-us/library/ms186755.aspx

-The following statements are valid in a function:
........
EXECUTE statements calling extended stored procedures.

==================================================

CREATE FUNCTION dbo.f_GetAnalysis
(
@group varchar(25),
@mode varchar(12)
)
RETURNS table
AS
RETURN
EXEC dbo.usp_GetAnalysis @mode , @group
go


Any ideas appreciated.

Post #790318
Posted Friday, September 18, 2009 6:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:45 AM
Points: 5,018, Visits: 10,536
If you just want to query the stored procedure results, leave the function alone, way too complicated.

You could set up a loopback linked server (ilnked server pointing to the server itself) and the run something like:

SELECT theColumnsYouNeed
FROM OPENQUERY(LOOPBACK, 'EXEC dbo.usp_GetAnalysis @mode = ''mode'' , @group = ''group'' ')

As I said, don't try to put it in the function, because it would simply ignore any data modification instruction contained in the stored procedure, WITHOUT raising any error. It's surprising, but it's so. Functions are not allowed to modify data.

Hope this helps.
Gianluca


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #790323
Posted Friday, September 18, 2009 6:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 18, 2009 8:53 AM
Points: 10, Visits: 61
thanks - never knew about this technique.

i will have to take your word for it as i need to get the DBA's to set this up for me.

i'll will post result once tested.

Post #790328
Posted Friday, September 18, 2009 6:43 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:07 PM
Points: 579, Visits: 1,807

AS per BOL,
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS @return_variable TABLE < table_type_definition >

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

BEGIN
function_body
RETURN
END



In multi-statement table-valued functions, function_body is a series of Transact-SQL statements that populate a table return variable.


The following statements are allowed in the body of a multi-statement function.


May be after RETURN statement that part is not considered as function body.

I think you may have to create a temp table/table variable and fill the data and then return it.


---------------------------------------------------------------------------------
Post #790330
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse