Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Creating view from stored procedure results Expand / Collapse
Author
Message
Posted Monday, January 4, 2010 2:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 28, 2013 5:28 AM
Points: 42, Visits: 137
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
Posted Monday, January 4, 2010 2:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Posted Monday, January 4, 2010 3:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 28, 2013 5:28 AM
Points: 42, Visits: 137
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
Posted Monday, January 4, 2010 4:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Posted Tuesday, January 5, 2010 9:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:58 AM
Points: 50, Visits: 633
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
Posted Tuesday, January 5, 2010 10:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Posted Tuesday, January 5, 2010 10:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:58 AM
Points: 50, Visits: 633
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
Posted Tuesday, January 5, 2010 10:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Posted Tuesday, January 5, 2010 10:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:58 AM
Points: 50, Visits: 633
Chris,

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

Thanks!



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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 28, 2013 5:28 AM
Points: 42, Visits: 137
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 12»»

Permissions Expand / Collapse