Calling a sp from a sql function

If you are aware of calling a sp from a function , please share

You cannot. See here

What problem are you trying to solve?

thanks

i have a sp returning a conversion value, which i have to use in a select query, so thought of calling it using a wrapper function, iam not the owner of the sp hence i cannot convert the sp to function

You can insert the results of the SP into a temporary table or table variable, then use it in your outer query.

Instead of 'converting' the SP to a function - create a new function that does the same thing as the procedure. When building this new function make sure you create it as an inline instead of multi-statement (if possible). Then you can outer/cross apply to the function to get the results in your query.

OK, hard problem to solve.

Best would be not to call the SProc at all - its going to be slow, and scale badly (assuming there are a decent number of rows in the Query that need processing and/or the Query will be used often, and in particular if the number of row in the query will grow over time). I'll assume that's not possible ...

I would get the results from the basic query into a #TEMP table, with an IDENTITY column, and then loop around (on the ID value) and EXEC the SProc for each value and then UPDATE #TEMP with the returned value. If it is possible that you will have the same @Parameter value multiple times in the #TEMP - i.e. such that the SProc would give the same result, you can update all matching rows and then skip them when that ID's turn comes in the Loop.

If you think that's the only way and need help with the code then please ask :slight_smile:

I definitely would not go this direction unless it was the last resort - and I had exhausted all other means of getting the results. It will depend on what that stored procedure is doing to provide the conversion value...

As I stated before - create a new function (inline table valued function) to perform the same conversion process as the stored procedure and outer/cross apply that into the query.

If there is no other way - then a CURSOR can be used and I would recommend using a CURSOR and not a temp table with a WHILE loop which is really just a cursor without declaring the CURSOR.

I agree, in case not clear I meant to convey that sentiment with: "Best would be not to call the SProc at all" and "If you think that's the only way"

I agree that's fine in principle, but it seriously depends on what the SProc is doing. If its some trivial calculation then fine, if its some convoluted processing then unlikely to be able to duplicate it in a function.

But even if it is easy to convert the Sproc to a function there is also the issue that the logic for the process then exists in two places - the original, 3rd party, SProc and the local function. No way of keeping the two in step automatically / sharing code, so that will be an accident waiting to happen.

I am sceptical that a cursor will be faster than a loop, happy to be proven wrong though. I'm assuming that all we are doing is getting a value(s) from #TEMP and passing that to an SProc and then updating a column in the #TEMP row with the result. But if the number of #TEMP rows involved is small the performance difference is probably negligible, either way, so if that's the case I'd go with whatever is most comfortable/reliable [for the DEV] / easiest to code/maintain.

you can't call a stored procedure from a function. function doesn't support to exec sp_

Ermmm ... as @JamesK said in the first reply to the original post, no?

I agree with your earlier points - and yes there is maintenance of the function and whether or not you can - in fact - get it to work. The thing here is that the OP is calling code from a 3rd party application - and doing that means you are tied to what the 3rd party decides to do...if your organization wants something slightly different from the 3rd party calculations then you have to create a custom version anyways.

Also - since this is a 3rd party calculation then you should only need to worry about it when you upgrade the 3rd parties application/system. So it really isn't too bad unless you are upgrading every other week.

To the CURSOR vs LOOP discussion - I prefer declaring the CURSOR and using the system to loop over the results as opposed to building all of the logic myself which has to be done if you build a WHILE loop. Declaring the cursor as STATIC and FORWARD_ONLY has SQL Server building a temp table for you - and loops over the table regardless of 'key' values. In a WHILE loop you have to identify each row and pull each row in your loop which means you need to identify a key value or add an identity value...much more work has to go into your code...

They may perform the same but using the cursor code it becomes much easier to manage and maintain.

My thought is that quite a lot depends on what you are familiar with. A LOOP would be familiar to someone who programs in a conventional language - so might be completely unfamiliar to someone who's whole life is Relational :slight_smile:

Just as a discussion point here's what I dislike about Cursors:

Some knowledge required. Things like "STATIC and FORWARD_ONLY" - there was a question posted the other day where the answer (to why it wasn't working) was that those hints had not been provided. For anyone used to using Cursors this is a Non Issue of course.

The duplication of code logic. We try strenuously to avoid that because it leads to bugs, most commonly during subsequent code maintenance (change one and not the other)

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       ... stuff ...

       FETCH NEXT FROM db_cursor INTO @name   <<< DUPLICATED CODE
END   

you can program around that, and only have one FETCH NEXT inside ... but that's more like a LOOP then ...

Final thing, I also dislike that the Declaration is "remote" from the Fetch. Risk of the columns in the Declare being misaligned with those in the Fetch (and between the two Fetch statements as already said)

DECLARE db_cursor CURSOR FOR  
SELECT name, phone, fax, town, zipcode, addr1, addr2, country, status
FROM MyTable

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name, @phone, @fax, @town, @addr1, @addr2, @zipcode, @country, @status

whereas a LOOP would be

SELECT @name = name
       , @phone = phone
       ...

so to my defensive-programming-eye better likelihood of correctly aligning @Params and column data

This isn't all the code you need for a WHILE loop - you need to create the temp table with either a SELECT INTO or CREATE and INSERT. You also need to identify the key values and how to increment those values to get the appropriate next row - or you have to SELECT TOP 1 with an ORDER BY and a following statement that deletes from the temp table (queuing process) based on some key value. All of this additional coding is required so you can advance through each row one at a time...

The CURSOR approach handles those details for you - so the only code you have to be worried about maintaining is the code that executes the stored procedure. The rest of the code is standard cursor setup and only changes if you modify the query definition to include additional parameters to be passed to the stored procedure.

A cursor in this situation - from my perspective - is the appropriate usage of cursors.

Indeed, I was merely illustrating that there was less chance of misalignment of @Parameters than when using a cursor where the DECLARE and (usually) two sets of FETCH statements are in different parts of the code.

Both are incorrect. You CAN call a stored procedure from within a function by using OPENROWSET. Unfortunately, the only way to pass parameters to the stored procedure in OPENROWSET would be to use dynamic SQL in the function, which I've not yet been able to figure out a way to do. :wink:

As a bit of a sidebar, there may be some money to be made here. Rewrite the 3rd part sproc as an iTVF and then offer to sell it to them. If they balk, send them the performance and resource usage stats. If they still balk, call come friends that also use the software and tell them to complain about the performance and resource usage. :wink: