How to create a view and select from it in one SINGLE query execution

Question

I want to create a veiw and then select from it in one query, would you please tell me how it is possible the code below:

create view myView as
select [Order Details].Discount from [Order Details]
select * from myView

if i want to run the code, first i have to select that part which is related to creating a view and then i have to select the next part which is related to selecting from that view. i wanted to know the way that both those queries run together when we execute the query the view would be created and then the select executes.

now i want to add that if once i create that veiw the other time SQL gives me error. how can i fix this?? mean that the object will be created just one time

Answer 1

Just write down Go after the command will work for you

create view myView as
select [Order Details].Discount from [Order Details]
Go
select * from myView
Go

you can also use CTE if the view is not necessary

-- Define the CTE expression name and column list.
WITH Sales_CTE (Discount)
AS
-- Define the CTE query.
(
    select [Order Details].Discount from [Order Details]
)
-- Define the outer query referencing the CTE name.
SELECT *
FROM Sales_CTE

Answer 2

You can also use execfor this

IF OBJECT_ID('dbo.myView','V') IS NULL
    EXEC ('create view dbo.myView as
    select [Order Details].Discount from [Order Details]
    ')

SELECT * FROM myView