Create View by select * from exec(MySP)

I created Pivot Table with dynamic number of columns however to achieve that I needed to create a string Query and run it using Exec('MyStringQuery'), that was the only way I could get the result I wanted.

Now I want to create a View using my Pivot table. Since in View I cannot run Exec(myQry)

I thought to create a function and run my query there and then use it in my View but I cannot create a table variable to put the result in there, because number of my columns my vary each time I run the query.

How can I achieve this?

create View myView
as
select * from exec(MySP)

It gives me an error when I try to run it.

Would appreciate your input very much.

Answers


I finally found an answer to my question. I created a Store procedure and used OpenRowset and created my view

Create MyView

AS

SELECT  *FROM 

  OPENROWSET( 'SQLNCLI',
                    'Server=SERVERNAME;Trusted_Connection=yes;',
                    'SET FMTONLY OFF; SET NOCOUNT ON; exec DatabaseName.dbo.MySP @A=1,@B=2')

You can't do this with a view, sorry. You can't pass parameters to a view, nor can you use dynamic SQL in a view. You also can't execute dynamic SQL in a function, so it won't work there. You'll need to call a stored procedure to do this I'm afraid.


Need Your Help

Run Script on logout/ shutdown ubuntu

linux ubuntu gnome logout

I want to run some commands every time a user of an ubuntu 9.10 machine logs out or shuts down. What is the best way to do this? Every user uses gnome if that helps.

API time resolution limiting USB bandwidth?

qt timer usb

I have a device that I can control how fast I send data over it with USB. I am using Qt for the PC side processing of the data. I am reading from the device every so often based on a timer. The pro...