CODEWITHSUNDEEP
sqlsql-serverview
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Store result of view as a variable and then call it in same query

Is something like this possible in SQL server?

DECLARE @SQL int  
SET @SQL = (CREATE VIEW AS SELECT *
        FROM .....
        JOIN.....)

EXEC @SQL

SELECT * FROM 

@SQL

LEFT JOIN.....
ON....

I want to join on the result of a view.

And then to actually select the variables such as

join ..... on @SQL.No = ......

Upvotes: 0

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Your code doesn't make sense. Why would you want to create a view using dynamic SQL? You can just create a view as:

CREATE VIEW my_view AS
    SELECT *
    FROM ..... JOIN
         .....;

You can then use the view in a query:

select . . .
from my_view join
     . . .

No dynamic SQL is required.

If you want a "temporary view" for a query, you can use a CTE or subquery.

Upvotes: 1

sapi
sapi

Reputation: 244

Try this : 

DECLARE @SQL nvarchar(max) ,
        @viewName nvarchar(50) = 'MyView'

SET @SQL = ('CREATE VIEW ' +@viewName  +'AS SELECT *
            FROM .....
            JOIN.....')

 EXEC sp_executeSQL @SQL

 SET @SQL = ('
 SELECT * FROM ' +  @viewName +'
 LEFT JOIN.....
 ON.... ')

 EXEC sp_executeSQL @sql

Upvotes: 0

Related Questions