Creating a view within a procedure

[collierd]

Hello

Can anybody help with this.
I am trying to create a view within a procedure as follows:



use MDW

if exists (select name from sysobjects
where name = 'Create_Stock_Type' and type = 'P')
drop proc Create_Stock_Type

go

create proc Create_Stock_Type
as
set nocount on -- doesn't return counts

begin transaction
set transaction isolation level serializable
commit

begin transaction
if exists (select TABLE_NAME from INFORMATION_SCHEMA.VIEWS
where table_name = 'STOCK_TYPE')
drop view STOCK_TYPE
commit

create view STOCK_TYPE as
select cast(st.salestypeid as int) as 'SALES_TYPE_CODE',
cast(st.salestypedesc as varchar(40)) as 'SALES_TYPE_DESC',
cast(st.prodordid as int) as 'PROD_ORD_TYPE_CODE',
cast(pot.prodordtypedesc as varchar(30)) as 'PROD_ORD_TYPE_DESC',
cast(pot.prodtypeid as int) as 'PRODUCT_TYPE_CODE',
cast(pt.prodtypedesc as varchar(20)) as 'PRODUCT_TYPE_DESC'
from salestype st
left outer join productordertype pot on st.prodordid = pot.prodordtypeid
left outer join producttype pt on pot.prodtypeid = pt.prodtypeid

go




This returns an error 'Incorrect syntax near the keyword view'
It works up to the view creation line (comment out and run)
Does anybody know what is going wrong here?
It works if I create it as a normal table creation but not as a view?
Is this possible?

Thanks

Damian.

 

[ThomVF]

Do you need a "go" after you "drop view stock type"??

begin transaction
if exists (select TABLE_NAME from INFORMATION_SCHEMA.VIEWS
where table_name = 'STOCK_TYPE')
drop view STOCK_TYPE
commit
go =================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[tlbroadbent]

"Go" would end the Batch and thus the stored procedure create statement. What is the database and which version? 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.

 

[collierd]

I take your point on 'go' Terry
It is SQL 2000

 

[tlbroadbent]

SQL Server does not allow Create View in a Stored procedure because Create View must be the first and only command in a batch. The same restriction applies to other commands such as Create Procedure and SET SHOWPLAN_TEXT.

You can use dynamic SQL to create the view in a stored procedure. Note that the quotes around the column names are unnecessary and I have removed them in this example. I recommend removing the Begin Transaction and Commit lines from the SP. These are not necessary and clutter the code.

Exec('create view STOCK_TYPE as
select cast(st.salestypeid as int) as SALES_TYPE_CODE,
cast(st.salestypedesc as varchar(40)) as SALES_TYPE_DESC,
cast(st.prodordid as int) as PROD_ORD_TYPE_CODE,
cast(pot.prodordtypedesc as varchar(30)) as PROD_ORD_TYPE_DESC,
cast(pot.prodtypeid as int) as PRODUCT_TYPE_CODE,
cast(pt.prodtypedesc as varchar(20)) as PRODUCT_TYPE_DESC
from salestype st
left outer join productordertype pot
on st.prodordid = pot.prodordtypeid
left outer join producttype pt
on pot.prodtypeid = pt.prodtypeid')
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.