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

Calling a stored procedure from within a function Expand / Collapse
Author
Message
Posted Monday, April 3, 2006 5:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 23, 2011 3:27 AM
Points: 1, Visits: 17

Hi,

I am trying to call a user defined stored procedure from within a function I have created.  I need to return the value of the stored procedure.  When I try to do this I get an error

'Only functions and extended stored procedures can be executed from within a function.' 

Could anyone post me an example of how to do this as I am stuck?

Thanks,

Paddy

Post #270460
Posted Monday, April 3, 2006 6:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:26 AM
Points: 265, Visits: 185

The error message is pretty explicit.  You cannot do that.

If you want to make call to another stored procedure, change your function to be a stored procedure and then you can make the call OR if you do need a function (calling from within a select/DML statement could be one of the reasons for needing the function), then create another function that can be called within this function body.

 

Post #270475
Posted Tuesday, April 4, 2006 3:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, Visits: 32

An interesting error message nonetheless, because in SS2K you can't use really useful system functions like getdate() from within a UDF.

...even more annoying, you can't even pass getdate() into a datetime function parameter.

David



If it ain't broke, don't fix it...
Post #270780
Posted Thursday, April 27, 2006 1:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 9:44 AM
Points: 35, Visits: 49

hello,

i hope that you found the answer to your question. but you still haven' t found it here is a possible solution:

you can actually call a sp from an udf by using the openquery(). the trick is that you can use openquery within a udf and sql do not parse the character string that you give as parameter to the openquery and the best thing is that you can refer to your own server when using openquery. to be able to do so you have to execute the following script:

EXEC sp_serveroption [server_name], 'Data Access', true
and here is an example how to call sp with openquery:
select * from openquery(MyServer, 'exec sp_who')go
if you post more details about what you are trying to do with your
udf a more elegant and straightforward way could be found but i hope
this helps
Post #276007
Posted Wednesday, July 5, 2006 2:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:25 AM
Points: 3, Visits: 25

Hi,

I have a udf. I need to call a sp on the same server. I tried using the above code. But I am getting errors in the secnd step

select * from openquery(MyServer, 'exec sp_who')go

Can u send me some sample code.

Thanx

Post #292341
Posted Wednesday, July 26, 2006 4:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 9, 2008 12:38 AM
Points: 1, Visits: 8

i have the same problem. i need to call a sp in a udf.

this is the function i wrote on your example:

CREATE FUNCTION dbo.c_sel_produs_comF_f
(@furnizor char(14), @data_doc smalldatetime, @tip char(1))
RETURNS TABLE
AS
RETURN
(SELECT * FROM openquery('SERVER', 'exec dbo.c_sel_produs_comF @furnizor, @data_doc, @tip'))

 

edit: there is something else: OPENQUERY does not accept variables for its arguments

Post #297361
Posted Friday, February 27, 2009 6:19 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 29, 2009 6:05 AM
Points: 7, Visits: 24
if you want sp_executesql with variables.
openquery do not allow any var's.

CREATE function [dbo].[create_SQL_string](@driver nvarchar(32), @sql nvarchar(max))
returns nvarchar(max)
with execute as caller
as begin
declare @out_sql nvarchar(max)
return 'select * from openquery('+@driver+', '''+REPLACE(@sql,'''','''''')+''')'
end


declare @a nvarchar(512)
set @a = (select [dbo].[create_SQL_string](N'SRV', @query))
exec sp_executesql @a

Post #665651
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse