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