Views in Stored Procedure

Question

Post reply

Views in Stored Procedure

Manish_

Ten Centuries

Points: 1070
More actions
September 10, 2005 at 1:03 am

#165193

Hi Friends

I am facing this below problem when i tried to create view in a stored Procedure on SQL SERVER EnterPrise Manager.

CREATE procedure TEST as

begin

Create view vvv as select * from mmm

end

GO

Error 156 : Incorrect syntax near the keyword 'view'

- Manish

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

sushila

SSC-Dedicated

Points: 35293

September 10, 2005 at 8:41 am

#588961

Manish - you cannot create a view (or a trigger) within a stored procedure...you would be better off creating your view in your database and selecting from it in your stored procedure. What exactly is your requirement ?!

**ASCII stupid question, get a stupid ANSI !!!**

Manish_ Ten Centuries Points: 1070 More actions · Answer 2

Hi Shushila.

First: of all thanks for your reply.

Secondly: My requirement is to create a view , and after doing some task there , i want to drop the view , within the stored procedure only. as i don;t want to keep that any more in the data base.

Finally : I tried to drop the view in Stored Procedure. it worked. so i m 100 % sure that there is some way to create view too.

- Manish

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Manish - yes, you can drop a view - not create it. The only way I know where you can create a view inside a stored procedure is to do so dynamically... something like this:

create procedure testCreateView
as

declare @strSQL varchar (600)

set @strSQL = 'create view vwTemp as select * from myTable'

exec (@strSQL)

select * from vwTemp

drop view vwTemp

go

If the requirement is to just temporarily create a resultset why don't you use a table variable in your stored procedure ?! Here's info from BOL on the pluses of using table variables"

"A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.

Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements.

table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.

table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.

Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources."

Here's an article on the curse & blessings of dynamic sql that you may want to read for reference!

**ASCII stupid question, get a stupid ANSI !!!**

Manish_ Ten Centuries Points: 1070 More actions · Answer 4

Hi Shushila

Thanks for your guidance.

and i too get the way to create view inside the stored procedure.

CREATE procedure UpdateCL as

SET NOCOUNT ON

begin

Exec ('Create view Vxxx as Select * from Test')

end

its working fine.

and the concept of the table variables u told me very nice i will definately do by that way in future.

Once again thanks for your time for me.

have a great day

- Manish