Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » T-SQL » Creating view from stored procedure results
Add to briefcase
14 posts, Page 1 of 2
12»»

Creating view from stored procedure results Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Raghunath Garlapati
Posted Monday, January 04, 2010 2:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:59 AM
Points: 42, Visits: 134
Hi All,

Is it possible to create a view from stored procedure , like i need to create a view which should return the result set of procedure.

/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
Post #841332
ChrisM@Work
Posted Monday, January 04, 2010 2:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 5,705, Visits: 11,136
Can you explain the context?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #841340
Raghunath Garlapati
Posted Monday, January 04, 2010 3:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:59 AM
Points: 42, Visits: 134
Hi,

I have a tool in .NET which accepts only views but i have some procedures which returns some result sets but Id is common in all the result set so i need to make one proc to join all the results sets depending on Id and make one result set (Ithought of using table variable) and finally from this procedure i need a view.

/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
Post #841358
ChrisM@Work
Posted Monday, January 04, 2010 4:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 5,705, Visits: 11,136
grngarlapati (1/4/2010)
Hi,

I have a tool in .NET which accepts only views but i have some procedures which returns some result sets but Id is common in all the result set so i need to make one proc to join all the results sets depending on Id and make one result set (Ithought of using table variable) and finally from this procedure i need a view.


That's quite a limitation.

The output from a stored procedure can be streamed into an existing table using SELECT .... INTO .... FROM EXEC ..., however the INTO keyword isn't allowed in views. There may be some mileage in creating table-valued functions from the stored procedures, but if the stored procedures are parameterised then this will create a whole new set of problems - which you would have to work around anyway.
I'd recommend finding a different tool, one which can cope with stored procedures: failing that I'd recommend rewriting the whole set of sprocs as one or more views.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #841381
mar1jxm
Posted Tuesday, January 05, 2010 9:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 07, 2013 3:21 PM
Points: 49, Visits: 554
Here it comes...

SELECT TOP (100) PERCENT *
FROM OPENROWSET('SQLOLEDB', 'Trusted_Connection=Yes;Server=(local);Database=Your_DB', 'exec usp_MySP)
AS derivedtbl_1


Enjoy!


Jacob Milter
Post #842196
ChrisM@Work
Posted Tuesday, January 05, 2010 10:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 5,705, Visits: 11,136
Sometimes...

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource'

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #842204
mar1jxm
Posted Tuesday, January 05, 2010 10:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 07, 2013 3:21 PM
Points: 49, Visits: 554
Chris,

Thanks!... but it works... If not, please use http://blog.sqlauthority.com/2008/01/02/sql-server-fix-error-15281-sql-server-blocked-access-to-statement-openrowsetopendatasource-of-component-ad-hoc-distributed-queries-because-this-component-is-turned-off-as-part-of-the-se/

Jacob


Jacob Milter
Post #842209
ChrisM@Work
Posted Tuesday, January 05, 2010 10:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 5,705, Visits: 11,136
Hi Jacob

Sure it will work - I think you can use the same trick to get NEWID() into a function. Many sites however don't allow it (openrowset, linked servers etc).

Cheers

ChrisM

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #842216
mar1jxm
Posted Tuesday, January 05, 2010 10:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 07, 2013 3:21 PM
Points: 49, Visits: 554
Chris,

Got it!
At least he will have a possible solution!

Thanks!


Jacob Milter
Post #842223
Raghunath Garlapati
Posted Tuesday, January 05, 2010 8:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:59 AM
Points: 42, Visits: 134
Hi All,

Thank you very much for above solution , Iam actually waiting for that ..... I will try that immediately.

Once again thank you for your solution

/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
Post #842522
« Prev Topic | Next Topic »

Add to briefcase
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.