Syntax error in user defined stored procedure - drop and creat views

[monkle]

I am trying to write a stored procedure to be called from a utility application on a dedicated server. I know that the create view statement alone is correct, but when placed in the context of a stored procedure, I get a syntax error. I do pretty much the same thing with a set of tables, and it works fine. Is it possible to create views using a stored procedure? If so, what am I missing about the syntax? I've been wrestling with this one for a while, any input would be greatly appreciated.

Query in question:

CREATE PROCEDURE dbo.CreateDEViews 
AS 
IF .dbo.TableExists('vwPickDvdNew')=1 
	DROP VIEW vwPickDvdNew 
IF .dbo.TableExists('vwPickDvdUsed')=1 
	DROP VIEW vwPickDvdUsed 
IF .dbo.TableExists('vwPickVhsNewandUsed')=1 
	DROP VIEW vwPickVhsNewandUsed 

CREATE VIEW dbo.vwPickDvdNew AS
	SELECT dbo.PickTable.* 
	FROM dbo.PickTable 
	WHERE (fermat = 'DV') 
CREATE VIEW dbo.vwPickDvdUsed AS 
	SELECT dbo.PickTable.* 
	FROM dbo.PickTable 
	WHERE (fermat = 'PD') 
CREATE VIEW dbo.vwPickVhsNewAndUsed AS 
	SELECT dbo.PickTable.* 
	FROM dbo.PickTable 
	WHERE (fermat = 'V') OR (fermat = 'PV')

RETURN Null

Errors:

Server: Msg 156, Level 15, State 1, Procedure CreateDEViews, Line 10
Incorrect syntax near the keyword 'VIEW'.
Server: Msg 156, Level 15, State 1, Procedure CreateDEViews, Line 14
Incorrect syntax near the keyword 'VIEW'.
Server: Msg 156, Level 15, State 1, Procedure CreateDEViews, Line 18
Incorrect syntax near the keyword 'VIEW'.

 

[SQLDenis]

I see some dots (.) if front of dbo
IF .dbo.TableExists('vwPickDvdNew')=1
DROP VIEW vwPickDvdNew
IF .dbo.TableExists('vwPickDvdUsed')=1
DROP VIEW vwPickDvdUsed
IF .dbo.TableExists('vwPickVhsNewandUsed')=1
DROP VIEW vwPickVhsNewandUsed

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[SQLBill]

I believe you can only do one CREATE VIEW per batch.

-SQLBill

Posting advice: FAQ481-4875

 

[monkle]

SQLDenis:
I see no difference between having them there or not

SQLBill:
Thank you for your help. I will find a way to get the desired results without using a user defined stored procedure.

 

[SQLBill]

Try putting a GO between each CREATE VIEW statement and see if that works.

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Maybe this would be a good place to use dynamic sql.

Try this procedure. I think it will do what you expect it to.

Create PROCEDURE dbo.CreateDEViews 
AS 

IF Exists(Select * 
          from Information_Schema.views 
          Where Table_Name = 'vwPickDvdNew'
                and Table_Schema = 'dbo'
         )
    DROP VIEW dbo.vwPickDvdNew 

IF Exists(Select * 
          from Information_Schema.views 
          Where Table_Name = 'vwPickDvdUsed'
                and Table_Schema = 'dbo'
         )
    DROP VIEW dbo.vwPickDvdUsed 

IF Exists(Select * 
          from Information_Schema.views 
          Where Table_Name = 'vwPickVhsNewandUsed'
                and Table_Schema = 'dbo'
         )
    DROP VIEW dbo.vwPickVhsNewandUsed 

declare @sql VarChar(8000)

Set @SQL = 'CREATE VIEW dbo.vwPickDvdNew AS
    SELECT dbo.Bookings.* 
    FROM dbo.Bookings 
    WHERE (fermat = ''DV'') '

exec (@SQL)

Set @SQL = 'CREATE VIEW dbo.vwPickDvdUsed AS 
    SELECT dbo.PickTable.* 
    FROM dbo.PickTable 
    WHERE (fermat = ''PD'')'
Exec (@SQL)

Set @SQL = 'CREATE VIEW dbo.vwPickVhsNewAndUsed AS 
    SELECT dbo.PickTable.* 
    FROM dbo.PickTable 
    WHERE (fermat = ''V'') OR (fermat = ''PV'')'
Exec (@SQL)

Return Null

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom