Drop View, Create View and Stored Procedures 1

[cyberbiker]

From what I have read, it seems that I cannot create a view in SQL 7 using a stored procedure, but that I can drop a view using a stored procedure. Is this correct?
Just seems to be inconsistant and I am afraid I am mising something.
Thanks Terry (cyberbiker)

 

[tlbroadbent]

Terry,

The issue is that "Create View ..." as well as "Create Procedure ..." and other statements must be the only statment in a batch. When you attempt to create a procedure containing one of these statments, SQL server cannot process the script because you have two Create statements in the batch. This is not true of "Drop View ..." or 'Drop Procedure ..." These statments can occur in the same batch as other statements.

You can work around the limitation by using dynamic SQL. Create a variable, set the value to the "Create View" string and execute the statement. When the statement is executed, it runs in its own context so the batch will contain one statement - "Create View ..." Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cyberbiker]

Thanks Terry. That also clears up some other confusion I have had re stored procedures. Terry (cyberbiker)