Creating view from stored procedure results

Question

Creating view from stored procedure results

Raghunath Garlapati

Ten Centuries

Points: 1122
More actions
January 4, 2010 at 2:06 am

#214152

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.
********************************************************/

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

Raghunath Garlapati Ten Centuries Points: 1122 More actions · Answer 2

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 SSC Guru Points: 186127 More actions · Answer 3

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

mar1jxm Ten Centuries Points: 1303 More actions · Answer 4

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 SSC Guru Points: 186127 More actions · Answer 5

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

mar1jxm Ten Centuries Points: 1303 More actions · Answer 6

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 SSC Guru Points: 186127 More actions · Answer 7

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

mar1jxm Ten Centuries Points: 1303 More actions · Answer 8

Chris,

Got it! 😛

At least he will have a possible solution!

Thanks!

Jacob Milter

Raghunath Garlapati Ten Centuries Points: 1122 More actions · Answer 9

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.
********************************************************/

Raghunath Garlapati Ten Centuries Points: 1122 More actions · Answer 10

Hi Jacob,

Iam getting the following error

Msg 7357, Level 16, State 1, Line 1

Cannot process the object "exec CsvFull". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

and the script is

SELECT TOP (100) PERCENT *

FROM OPENROWSET('SQLOLEDB', 'Trusted_Connection=Yes;Server=(local);Database=Conti;user id=sa;password=password123$', 'exec CsvFull')

AS derivedtbl_1

Can you please tell me what mistake i have done in th above code or am i missing any thing....

/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/

Raghunath Garlapati Ten Centuries Points: 1122 More actions · Answer 11

Hi Jacob,

Its working thanqqqqqqqqqqqqqq

i tried like this , I have created linked server on my own and then the below one.Thanq for suggesting me linkedserver .........:-)

select TOP (100) PERCENT * from openquery([TEST],'Exec [CONTI].dbo.csvfull')

/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/

Korrapati SSC Veteran Points: 234 More actions · Answer 12

Korrapati

SSC Veteran

Points: 234

May 23, 2011 at 4:26 pm

#1328771

This is cool.

mar1jxm Ten Centuries Points: 1303 More actions · Answer 13

You are welcome!

The first syntax will work if you first run your sp manually (just 1 time). The script creates a structure and you are set to go! 😉

Jacob Milter