View limitations?

SQL Apprentice Question
I was trying to do a complex view with declares, sets that use selects, and
the main query which uses case statements. I've tried to create this view
using Enterprise manager and VS, but the result is always the same.

Once I have the whole shebang down, I run it to make sure it works, which it
does. However, when I try to save the view, I get two different errors:


VS: Incorrect syntax near the keyword declare
the view starts with the following lines:
DECLARE @roomId int, @cabinetSort tinyint
SET @roomId = ...


EM: Does not execute; I get no results, just a message box telling me one
row was affected by the query. Attempting to save, I get the error: View
definition includes no output columns or includes no items in the from clause


What am I missing here?



Celko Answers

>> What am I missing here? <<


The correct definition of a VIEW.

It is not a procedure with parameters or a code module with local
variables It is a virtual table that is defined by a single SELECT
statement, and it also allows the WITH CHECK OPTION clause.


You are still thinking that it is an executable procedural code module.
It is declarative, not procedural.