pzozulka
asked on
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.
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.
PortletPaul
a view on temp tables may also fail (when one or more of the tables aren't there)
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.