Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

How to create

Mats77Mats77 Member Posts: 2
Hello,
I'd liked to dynamically create a view in a procedure, but haven't been able to find the right syntax (SQL Server 7).

Here's the code :
CREATE PROCEDURE ChangeViewCleanCharacters_For_Table1Field1
AS
BEGIN
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'CleanCharacters' AND type = 'V')
DROP VIEW CleanCharacters
GO
Create view CleanCharacters as
select ...
GO
END

And here are the error messages :

Server: Msg 170 ...
Line 8: Incorrect syntax near 'CleanCharacters'.

Server: Msg 156 ...
Line 4 : Incorrect syntax near the keyword 'END'.

If I delete the keywords GO, I have now :
Server: Msg 156...
LIne 10 : Incorrect syntax near the keyword 'view'.

Thanks for the help,
Mats

Comments

  • Mats77Mats77 Member Posts: 2
    I have found an information saying that the Create View instruction is not allowed in a procedure. Anyway if I replace it by an Alter View, I still have an error message!



    : Hello,
    : I'd liked to dynamically create a view in a procedure, but haven't been able to find the right syntax (SQL Server 7).
    :
    : Here's the code :
    : CREATE PROCEDURE ChangeViewCleanCharacters_For_Table1Field1
    : AS
    : BEGIN
    : IF EXISTS (SELECT name FROM sysobjects
    : WHERE name = 'CleanCharacters' AND type = 'V')
    : DROP VIEW CleanCharacters
    : GO
    : Create view CleanCharacters as
    : select ...
    : GO
    : END
    :
    : And here are the error messages :
    :
    : Server: Msg 170 ...
    : Line 8: Incorrect syntax near 'CleanCharacters'.
    :
    : Server: Msg 156 ...
    : Line 4 : Incorrect syntax near the keyword 'END'.
    :
    : If I delete the keywords GO, I have now :
    : Server: Msg 156...
    : LIne 10 : Incorrect syntax near the keyword 'view'.
    :
    : Thanks for the help,
    : Mats
    :

Sign In or Register to comment.