SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calling a stored procedure from within a function


Calling a stored procedure from within a function

Author
Message
Paddy Hamilton
Paddy Hamilton
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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


rsharma
rsharma
SSC-Addicted
SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)

Group: General Forum Members
Points: 413 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.


David le Quesne
David le Quesne
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1049 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...
tebaida
tebaida
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 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

saty
saty
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 26

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
moni dragu
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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


KPC-720254
KPC-720254
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search