SP in a View

Hi ,
Is there any way to use a Sored Procedure in a View.
What I mean is
' To execute the stored procedure and output some columns that could be
called by the view'
Thanks

You could use a hack with OPENQUERY or OPENROWSET. I don't recommend that, t
hough.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/


"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166008494.370157.140690@j44g2000cwa.googlegroups.com...
> Hi ,
> Is there any way to use a Sored Procedure in a View.
> What I mean is
> ' To execute the stored procedure and output some columns that could be
> called by the view'
> Thanks
>

Dutt

CREATE PROC usp_myproc
AS
SELECT OrderId,OrderDate FROM Orders
GO

--usage
EXEC usp_myproc

Why would you want to call it by view, can you explain a little bit?




"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166008494.370157.140690@j44g2000cwa.googlegroups.com...
> Hi ,
> Is there any way to use a Sored Procedure in a View.
> What I mean is
> ' To execute the stored procedure and output some columns that could be
> called by the view'
> Thanks
>

Uri,

Here I'm concerned with performance tuning.
I'm using almost 8 joins(inner and left outer) in the SP.The
performance is a bit bad.
For a SP, since the execution plan is stored,its fast,isnt it....
So,what i think is i create SPs for 3 or 4 joins and connect all the
result sets through  'VIEWS'.
I hope u can understand me now.

Thanks
Dutt
Uri Dimant  wrote:[color=darkred
]
> Dutt
>
> CREATE PROC usp_myproc
> AS
> SELECT OrderId,OrderDate FROM Orders
> GO
>
> --usage
> EXEC usp_myproc
>
> Why would you want to call it by view, can you explain a little bit?
>
>
>
>
> "Dutt" <Mr.Dutt@gmail.com> wrote in message
> news:1166008494.370157.140690@j44g2000cwa.googlegroups.com... 

Uri,

Here I'm concerned with performance tuning.
I'm using almost 8 joins(inner and left outer) in the SP.The
performance is a bit bad.
For a SP, since the execution plan is stored,its fast,isnt it....
So,what i think is i create SPs for 3 or 4 joins ,use OUTPUT parameters
in it and connect all the result sets through  'VIEWS'.
I hope u can understand me now.

Thanks
Dutt
Uri Dimant  wrote:[color=darkred
]
> Dutt
>
> CREATE PROC usp_myproc
> AS
> SELECT OrderId,OrderDate FROM Orders
> GO
>
> --usage
> EXEC usp_myproc
>
> Why would you want to call it by view, can you explain a little bit?
>
>
>
>
> "Dutt" <Mr.Dutt@gmail.com> wrote in message
> news:1166008494.370157.140690@j44g2000cwa.googlegroups.com... 

Dutt
> I'm using almost 8 joins(inner and left outer) in the SP.The
> performance is a bit bad.
> For a SP, since the execution plan is stored,its fast,isnt it....
> So,what i think is i create SPs for 3 or 4 joins and connect all the
> result sets through  'VIEWS'.


No, I think you are on the wrong way. If you notice that perfomance is bad ,
try to invsetigate an execution plan   , perhaps the optimizer uses a wrong
/or not using at all indexes




"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166009956.577089.47590@f1g2000cwa.googlegroups.com...
> Uri,
>
> Here I'm concerned with performance tuning.
> I'm using almost 8 joins(inner and left outer) in the SP.The
> performance is a bit bad.
> For a SP, since the execution plan is stored,its fast,isnt it....
> So,what i think is i create SPs for 3 or 4 joins and connect all the
> result sets through  'VIEWS'.
> I hope u can understand me now.
>
> Thanks
> Dutt
> Uri Dimant wrote: 
>

> I'm using almost 8 joins(inner and left outer) in the SP.The
> performance is a bit bad.
> For a SP, since the execution plan is stored,its fast,isnt it....

The main reason for execution time is probably not producing the execution p
lan, it is probably due
to inefficient data access. My suggestion is that you attack this problem by
 looking at the
execution plan, analyze and consider creating supporting indexes for the que
ry.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/


"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166009956.577089.47590@f1g2000cwa.googlegroups.com...
> Uri,
>
> Here I'm concerned with performance tuning.
> I'm using almost 8 joins(inner and left outer) in the SP.The
> performance is a bit bad.
> For a SP, since the execution plan is stored,its fast,isnt it....
> So,what i think is i create SPs for 3 or 4 joins and connect all the
> result sets through  'VIEWS'.
> I hope u can understand me now.
>
> Thanks
> Dutt
> Uri Dimant wrote: 
>

Hi both,

In the execution plan itself I found that the JOINS are taking more
time.
Anyhow,I'll try again .

ThankYou friends
Dutt
Tibor Karaszi  wrote:[color=darkred
] 
>
> The main reason for execution time is probably not producing the execution
 plan, it is probably due
> to inefficient data access. My suggestion is that you attack this problem 
by looking at the
> execution plan, analyze and consider creating supporting indexes for the q
uery.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Dutt" <Mr.Dutt@gmail.com> wrote in message
> news:1166009956.577089.47590@f1g2000cwa.googlegroups.com... 

How did you know it? What kind of JOIN do you see at execution plan?




"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166014736.195956.305080@l12g2000cwl.googlegroups.com...
> Hi both,
>
> In the execution plan itself I found that the JOINS are taking more
> time.
> Anyhow,I'll try again .
>
> ThankYou friends
> Dutt
> Tibor Karaszi wrote: 
>

> In the execution plan itself I found that the JOINS are taking more
> time.

Which is why you would attach this issue like any SQL related performance is
sue. Look at the
queries, plans and how the queries uses the indexes you have. Perhaps you ju
st need to create
indexes on (some of) the columns that you joins over, for instance.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/


"Dutt" <Mr.Dutt@gmail.com> wrote in message
news:1166014736.195956.305080@l12g2000cwl.googlegroups.com...
> Hi both,
>
> In the execution plan itself I found that the JOINS are taking more
> time.
> Anyhow,I'll try again .
>
> ThankYou friends
> Dutt
> Tibor Karaszi wrote: 
>