How to create view from the output of a dynamic sql?

_{Wednesday, June 22, 2016 1:30 PM}

Hi,
I have a requirement to convert a normal table into a pivot table and I got the code to do it using Dynamic SQL. So, finally it ends like the below
   Exec(@sql).

Now, I wants to store the output into a 'view'. I am not sure how to create a view from the output of Exec (@sql).
Can you please help me?

_{Wednesday, June 22, 2016 2:05 PM ✅Answered}

Thanks -  I am trying to use the logic given by The SmilingDBA...But, am getting the error like The data types varchar and varchar(max) are incompatible in the '&' operator.

TheSmilingDBA messed up VB syntax and SQL syntax and used the wrong operator for concatenation. Use + instead.

However, Scott is right on the money. What you ask for is not possible and does not make sense in the realm of a relational database.

Yes, you can define a view on the query you generated with the dynamic SQL. But that view will always have the same set of columns. If you pivot on, say, product names, and you have the products Fingles, Bangles and Hooples today, the view is not going magically have a column for Scripples tomorrow when this column is added to the product catalog.

_{Wednesday, June 22, 2016 1:38 PM}

Add the Create View statement to the variable or new variable

Declare @DDLStatement varchar(max)

Set @DDLStatement = 'CREATE VIEW dbo.vwMyView AS ' & @sql & ' GO'

Exec @DDLStatement

TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

_{Wednesday, June 22, 2016 1:44 PM}

I wants to store the output into a 'view'.

Your choice of words is confusing.  A view is simply a prewritten query that is evaluated and executed at runtime.  One does not "store" output in a view, one stores output in a table. Assuming you want to convert your dynamic sql script into a view, the short answer is you can't.

_{Wednesday, June 22, 2016 1:48 PM}

thanks.

I got the below error

The data types varchar and varchar(max) are incompatible in the '&' operator.  Any help? Tnx!

_{Wednesday, June 22, 2016 1:54 PM}

Thanks -  I am trying to use the logic given by The SmilingDBA...But, am getting the error like The data types varchar and varchar(max) are incompatible in the '&' operator.

_{Wednesday, June 22, 2016 1:58 PM}

>incompatible in the '&' operator.

That's a typo.  '+' is the concatenation operator in TSQL.

David

David http://blogs.msdn.com/b/dbrowne/

_{Wednesday, June 22, 2016 1:59 PM}

Nobody can read your mind or see your screen.  Statements about receiving errors "like ..." do not help.  Dynamic sql is an advanced skill and it appears you are not yet ready to attempt such things.  In any event, you are not new so you should, by now, know how to post technical questions. No one can debug code that is not visible.

And I'll repeat - a view is composed of a single statement.  You cannot define variables or use multiple statements in a view.  Given your previous post on the same topic, you will not be able to use a view as a shortcut to your dynamic pivot logic.  You can use a stored procedure to do this - but you seem to have chosen this particular path and it is not clear why.

_{Wednesday, June 22, 2016 9:26 PM}

Sorry, here is some tested code:

declare @sql as nvarchar(max)
set @sql = ' SELECT * FROM [dbo].[bi_date]'
exec sp_executesql @sql

declare @tempsql as nvarchar(max)
set @tempsql = 'CREATE VIEW dbo.vwTemp AS ' + @sql
exec sp_executesql @tempsql

TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA