Create Views From Stored Procs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Create Views From Stored Procs

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 />
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 />
Bambola, Thanks for the quick response. i’ll try this ASAP. Anjo
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
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.
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=’:)‘ />]
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.
Not sure I am following you. Do you want to create a table/temp table in the stored procedure? Bambola.
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
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 />
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |