TSQL - Remove Views in SPs quickly
Yesterday I had a task in TSQL. Task is to eliminate views from SQL
procedures and use the tables instead (with the same logic of views) . i.e., SP
contains combination of tables and views, and these views also prepared with other tables and
views. Now I need to write a single SP with complete logic (by considering
nested views inside).
To make it easier, I followed this approach with
the help of my friend.
Sample:
create procedure SampleForYouGuys()
BeginSelect * from Table1 T
Left Join View1 vw1 on T.ID = Vw1.ID
End
CREATE VIEW dbo.View1
ASSelect * from View2 vw2
Left Join View3 vw3 on vw3.ID = Vw2.ID
.
..
Create View dbo.View10
ASSelect ......
Solution in Phase-I
create procedure SampleForYouGuys()Begin
Select * from Table1 T
Left Join
(
Select * from
(
Select * from Table2,table3
)
vw2
Left Join
(
Select * from Table3,table4,...
)
vw3 on vw3.ID = Vw2.ID
)
vw1 on T.ID = Vw1.ID
End
Like this first we can re-write the SP and check
the results (Obviously it would same J)
Due to this approach, existing UI/application wont be changed and it will work as expected and business wont’ be affected.
Phase-II solution, analyze the query (plz dont scold me :-)) and then
proceed. Because we need to consider too many things while we optimize these sory of queries right.
Comments