using stored prcedures within functions

Question

using stored prcedures within functions

eyal sacharov

Say Hey Kid

Points: 672
More actions
May 26, 2005 at 5:23 am

#87567

Hey,
Is it possible to use an sql stored procedure from within the scope of a user-defined function?
if so, how? if not: how can I achive the following:
I have a SP that returns a table. I need a function that will calulate
something for each member of this table.

Thanks,

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You can't exec a stored proc in a function. You'll have to copy the select in that function or convert the stored proc to a parametered function.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

Will this not work?

CREATE PROC foo

AS

SELECT dbo.dosomething(some_column) FROM sometable

Note that this will not perform very well, since the function is called once for each row. But that is the nature of UDFs. Maybe there is a better solution if you could describe exactly what you need, and post some DDL and sample data.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

eyal sacharov Say Hey Kid Points: 672 More actions · Answer 3

Thanks for replying...

my function (is supposed to) return a string, which is a list of id's. I want to use this in my IN clause in the stored procedure (i.e, SELECT ... WHERE id IN (dbo.MyFunction(@paramter1,...))

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Just do something like this :

Create function dbo.a (@someparam as int)

RETURNS TABLE

AS

return (

Select id from dbo.yourTable where Something=@Someparam

)

GO

Create procedure dbo.b @SomeParam as int

as

set nocount on

Select * from dbo.tableB where id in (Select id from dbo.a(@SomeParam))

set nocount off

eyal sacharov Say Hey Kid Points: 672 More actions · Answer 5

This looks like it might work. The thing is i'm trying to re-use an already made function. This function returns a string of id's ( a list ) . If only I could use SP inside a function...

Anyway, thanks, i guess i'll have to rewrite the whole thing.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Well you could always resplit the string but that would seem to be a lot of work for absolutely nothing. I think that the small task of copying the function, remove the concatenation part would be well spent.

Peter Edmunds Grasshopper Points: 22 More actions · Answer 7

Read BOL about deterministic functions. The root of the question is in non-deterministic functions not being allowed in the body of user-defined functions and an sp is purposely non-deterministic. Should be clear after getting your head around that idea 🙂