SQL: View vs. Function

I have an existing stored procedure (300 lines of code) for a report that creates two temp tables.

tempTable A gets all accounts that match a certain criteria. This table is then treated as a reference table.

tempTable B gets all accounts that match the reference accounts in tempTable A.

Lastly, a select statement is called to get all records from tempTable B.

There is now a need to modify an existing stored procedure called ABC for a different report, and I really don't want to COPY/PASTE 300 lines of code from one stored procedure to another, so I was thinking of creating a table-valued function and COPY/PASTE those 300 lines into it, and then just call this function from the ABC stored procedure.

However, I was also suggested that I could create a view instead, and perhaps a view could be more efficient?

I need your advice on how to design this further for stored procedure ABC.
LVL 8
pzozulkaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
a view on temp tables may also fail (when one or more of the tables aren't there)
0
Dale BurrellDirectorCommented:
An inline table valued function should perform just as well as a view, while allowing a greater level of control e.g. using parameters etc
0
jogosCommented:
First reading: copying the 300 lines of code from first procedure to UDF and call that UDF from old and new procedure means that you probably don't need a second procedure.  But guess that is to simple.

Different options and very based on what the difference is between procedures: criteria for table A, criteria or data for table B and/or the statement for your report data.  

There is the maintainability
- selection must be reliable, changes in one report must/may not  inpact in other report
- performance : touch as little data as possible from start to end of report


When criteria differ for the tempTable A
Make an (inline table valued ) UDF with parameters that returns the  selected accounts.  

Can A and B get combined?

Using views in a more complex situation (views on views, adding rownumbers....)  have the problem that the filter you apply on the end is translated in the usage of the proper index for that resultset.   For example you report on year or month-base your view does not know that you finnaly wil only filter the current month. An udf with a year/month parameter or daterange there the sql engine has less possibility to miss the index for that criteria.
If report A is all customers for last month then you make a udf that has date-parameters.   If report B is to report all customers for category x products then your filtering will be on a whole other level.

Separating filtering it in UDF's can then get the efect you touch same data on 2 occasions what may impact your performance.
0
Tomas Helgi JohannssonCommented:
Hi!

Using UDF's (user defined functions ) in views, other UDF's or SP is very common when
1 ) the SQL of a view becomes to complex
2 ) parts of the sql can be reused in elsewhere  ( code reuse :)  )

The only thing that matters is that the sql is as fast and efficient  as possible. :)

Stored Procedures are great for data and sql where a join in a view becomes impossible or is way to slow due to complex data casting/manipulation before a join could occur. In such case  temporary tables  in a SP may become necessary to hold and process the data to achieve desired results.

Regards,
    Tomas Helgi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.