SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating view from stored procedure results


Creating view from stored procedure results

Author
Message
Raghunath Garlapati
Raghunath Garlapati
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 138
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.
********************************************************/
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42392 Visits: 20012
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
Raghunath Garlapati
Raghunath Garlapati
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 138
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.
********************************************************/
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42392 Visits: 20012
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
mar1jxm
mar1jxm
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 656
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42392 Visits: 20012
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
mar1jxm
mar1jxm
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 656
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42392 Visits: 20012
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
mar1jxm
mar1jxm
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 656
Chris,

Got it! :-P
At least he will have a possible solution!

Thanks!


Jacob Milter
Raghunath Garlapati
Raghunath Garlapati
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 138
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.
********************************************************/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search