SQL Server Performance

Create Views From Stored Procs

Discussion in 'T-SQL Performance Tuning for Developers' started by Anjo, Jul 22, 2003.

  1. Anjo New Member

    Hi everyone,<br /><br />Am a newbie in SQL procs, i just wanna ask if it's possible to create a view using the result of a stored procedure. <br /><br /><font color="red">Something like this:</font id="red"><br /><br /><font color="purple">Create procedure sp_OutstandingEmps<br />@yr int<br />@qtr char(1)<br />AS<br />Select * from [OAASummary]<br />where Yr Like @yr And Qtr Like @qtr<br />GO<br /><br />Create view vw_OAASummaryCurrent</font id="purple"><br /><br />... don't know how to call the stored proc from here to create the view, and of course I have to check whether the view exist, if yes, drop the view, else create it...<br /><br /><font color="purple">GO</font id="purple"><br /><br />If somebody had done this, can you teach me how. Do you have any sample codes?<br /><br />Thanks a lot.<br />Anjo [<img src='/community/emoticons/emotion-12.gif' alt=':X' />]<br />
  2. bambola New Member

    In this case you can write a function to do this task. <br /><pre><br />IF EXISTS (SELECT * <br /> FROM sysobjects <br /> WHERE name = N'fn_OutstandingEmps')<br />DROP FUNCTION fn_OutstandingEmps<br />GO<br /><br />CREATE FUNCTION fn_OutstandingEmps(@yr int, @qtr char(1))<br />RETURNS TABLE <br />AS<br />RETURN <br /><br /> SELECT * <br /> FROM [OAASummary]<br /> WHERE Yr LIKE @yr AND Qtr LIKE @qtr<br /></pre><br /><br />You use it like this:<br /><br />SELECT * FROM dbo.fn_OutstandingEmps(@yr, @qtr)<br /><br />It is a better practice to specify the fields you need instead of using SELECT *.<br />When using LIKE if parameters does not contain wildcars you might not get the results you want.<br />I also suggest you avoid using sp_ prefix for this stored procedure. I will explain later the reasons. gotta rush back to work now <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Bambola.<br /><br /> <br />
  3. Anjo New Member

    Bambola,

    Thanks for the quick response. i'll try this ASAP.

    Anjo
  4. rushmada New Member

    Yes u can do this with linked server. U add linked server with . in the
    Enterprise manager of that system with sa privileges.

    Here . refers to the local server.

    Assume that auth is a procedure in pubs which consists of
    create proc auth
    as
    begin
    select * from authors
    end

    and u have created a view with name aa and u are caling the procedure
    auth in the view

    create view aa
    as
    SELECT *
    FROM OPENQUERY([.], 'exec [.].pubs.dbo.auth')
    go
    select * from aa



    Rushendra
  5. bambola New Member

    quote:Originally posted by rushmada

    Yes u can do this with linked server.

    It's true. I remember this unusal and very interesting solution was once offered by vbkenya.
    But I would not rush into using a linked server when there are other more simple and probably more efficiant solutions.

    Bambola.
  6. Anjo New Member

    Bambola,<br /><br />It worked. Another question, in my sample stored proc:<br /><br /><font color="red">Create procedure sp_OutstandingEmps</font id="red"><br /><font color="purple">@yr int<br />@qtr char(1)<br />AS<br />Select * from [OAASummary]<br />where Yr Like @yr And Qtr Like @qtr<br />GO</font id="purple"><br /><br />Instead of the select statement, can I just use create table OAAtemp? So that whenever I use the stored proc in my ASP.net code, the parameters passed is used and a table would be created. The table will then be used as my datasource for a certificate template (in MSword's mail merge).<br /><br />Any sample codes?<br />Hope am not too much of a bother,<br />Anjo [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  7. Chappy New Member

    Also id question whether you really need views or temp tables etc.

    Nothing youve posted so far suggests to me that you couldnt just use a stored procedure as you are doing, and return the result you want from that back into your ASP code.

    Indexed views can help improve performance, non indexed views can simplify complex database designs. But often a stored proc is the best solution for a simple parameterised query such as yours.
  8. bambola New Member

    Not sure I am following you. Do you want to create a table/temp table in the stored procedure?

    Bambola.
  9. Anjo New Member

    Bambola/Chappy<br /><br />Yes, I wanted to use the stored procedure to create a table, since I have the yr and qtr parameters to fill-in. I know this is a simple problem, but am just getting to know t-sql. Sorry for the bother, but if you can explain it, i would really appreciate the help.<br /><br />Thanks <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Anjo
  10. Anjo New Member

    Guys, already got the codes, thanks for the help. Bambola, have used your function effectively.<br /><br />Thanks,<br />Anjo <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />

Share This Page