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