September 18, 2009 at 6:29 am
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.
September 18, 2009 at 6:35 am
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
September 18, 2009 at 6:41 am
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.
🙂
September 18, 2009 at 6:43 am
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.
---------------------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply