exec stored proc inside a function

Question

Post reply

exec stored proc inside a function

londoner-737512

Old Hand

Points: 378
More actions
September 18, 2009 at 6:29 am

#226307

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.

Viewing 4 posts - 1 through 3 (of 3 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

londoner-737512 Old Hand Points: 378 More actions · Answer 2

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.

🙂

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

AS per BOL,

CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS @return_variable TABLE
[ WITH [ [,] ...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.

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