Calling a stored procedure from within a function

Question

Calling a stored procedure from within a function

Paddy Hamilton

SSC Veteran

Points: 221
More actions
April 3, 2006 at 5:21 am

#168901

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

Viewing 7 posts - 1 through 6 (of 6 total)

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

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 1

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.

David le Quesne SSCertifiable Points: 5229 More actions · Answer 2

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...

tebaida SSC Journeyman Points: 87 More actions · Answer 3

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

saty SSC Veteran Points: 291 More actions · Answer 4

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

moni dragu SSC Rookie Points: 49 More actions · Answer 5

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

KPC-720254 SSC Enthusiast Points: 109 More actions · Answer 6

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