Any Utility available to convert Sql Server Stored Procedures into user defined functions

Question

Any Utility available to convert Sql Server Stored Procedures into user defined functions

suneel-210238

SSC Enthusiast

Points: 193
More actions
June 17, 2009 at 7:36 pm

#222634

Hi,
Is there any utility avaialble that converts SPs into functions?
We got a requirement and in order to work it out, we have to convert all the Sps into functions.
Summary : Converting SPs to functions is not a good process.
Please help!
-Suneel

Viewing 12 posts - 1 through 12 (of 12 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First, why such a requirement? Second, I am not aware of any utility to accomplish this conversion. Third, if you rewrite the stored procedures as multi-lined table valued functions, it is quite possible that performance may be worse than using the stored procedures to populate temporary (# or ##) tables and writing queries and other processes against those temporary tables.

Remember, multi-statement tvf's will be treated by the query optimizer as if they have 1 row even if they have several 1,000 (or more) which means the query plans may not be the best.

Changing them for the sake of changing them is not necessarily the best way to go.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 2

Most of the time I've done this was for stored procedures that returned an OUTPUT type parameter, which can mostly be replaced by scalar functions. I'm afraid there is no utility that will do this.

Todd Fifield

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 3

Like Lynn, I'd question that requirement. I'm not sure I can think of a good reason someone would want this done.

Get some reasoning and perhaps we have other advice or suggestions.

suneel-210238 SSC Enthusiast Points: 193 More actions · Answer 4

Hi

We have list of 50 sps that need to be executed and export the results into excel and each worksheet contains resultset of one sp.

we use these sps from asp.net and execute one by one and finally we import them into excel file with one sp for one worksheet.

we got a new requirement that only few columns need to be displayed in the worksheet for some sps and this leaded to convert sps into functions to fetch columns at the run time.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

It's probably not the answer you want to hear but, there's a large amount of functionality in many stored procedures that simply cannot be migrated to a function. Just about any SET option will fail, as one of many possible examples. The use of certain functions is also forbidden in UDF's. There are many more examples of where things that appear in stored procedures simply have no working equivalent in UDF's.

You guys are in for a hell of a ride if they force you to do this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

suneel-210238 SSC Enthusiast Points: 193 More actions · Answer 6

Jeff,

thanks for your alert. We are making sure that none of the functions will not fail after the conversion.

But will you suggest me anything to get out of this hell.

thanks in advance.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 7

The only suggestion I have isn't the one they want to hear. Don't do the conversions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 8

I agree with Jeff. You haven't provided a reason why to use functions and not stored procedures. From the calling program they look the same.

The number of columns returned has nothing to do with the structure in SQL Server. It sounds like someone learned a new word (functions) and wants to try it out. Not a good reason for the conversion.

suneel-210238 SSC Enthusiast Points: 193 More actions · Answer 9

Hi,

As I mentioned, we import the resultset of these SPs into Excel. But the columns that we display in each and every worksheet are dynamic. So, we are trying to send the filtered data to asp.net so that it does not need to loop thru the columns of each and every datatable and remove those columns that are not required to display.

Here we need to filter the data by columns.

We have already reverted back the conversion process and wriiten a wrapper SP that returns resultsets of two tables one will be the actual data and the other table that returns the columns that should not be shown.

Please let me know if you have any optimized solution.

-Suneel

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 10

suneel (6/22/2009)
Hi,
As I mentioned, we import the resultset of these SPs into Excel. But the columns that we display in each and every worksheet are dynamic. So, we are trying to send the filtered data to asp.net so that it does not need to loop thru the columns of each and every datatable and remove those columns that are not required to display.
Here we need to filter the data by columns.
We have already reverted back the conversion process and wriiten a wrapper SP that returns resultsets of two tables one will be the actual data and the other table that returns the columns that should not be shown.
Please let me know if you have any optimized solution.
-Suneel

Seems like either a little dynamic SQL would do the trick or simply creating a view that uses OPENQUERY would do it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 11

What about piping the sp result set into a temp table and the selecting just the columns you need?

Something like:

CREATE TABLE #tempTable (column1 int, ... columnN int)

INSERT #tempTable EXEC myProcedure

SELECT justTheColumnIWant

FROM #tempTable

-- Gianluca Sartori