Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Can't create a stored procedure
Message
 
À
Tous
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Titre:
Can't create a stored procedure
Divers
Thread ID:
01041604
Message ID:
01041604
Vues:
53
In Query Analizer I am trying this:
     IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'SpAldinDoors' AND type = 'P')
        BEGIN  
   	   CREATE PROCEDURE SpAldinDoors 
           AS
           DECLARE @myTable AS Char(250)

           SELECT @myTable=table_name 
                  FROM INFORMATION_SCHEMA.TABLES
                  WHERE table_type = 'BASE TABLE'  AND table_name = 'ALADINDOOR'

           IF @myTable IS NULL
              CREATE TABLE AladinDoor (Kod Int, Name nvarchar(25), Vid smallint)

           INSERT INTO AladinDoor
           SELECT (Router*1000+Reader_1), Description, 255 FROM Door WHERE (Door.Router*1000+Door.Reader_1) NOT IN (SELECT Kod FROM AladinDoor)

           INSERT INTO AladinDoor
           SELECT (Router*1000+Reader_2), Description, 255 FROM Door WHERE (Door.Router*1000+Door.Reader_2) NOT IN (SELECT Kod FROM AladinDoor)

           SELECT * FROM AladinDoor
           GO
        END 
But the result is
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'PROCEDURE'.
Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 'AladinDoor'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.

Why?
If I try:
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SpAldinDoors' AND type = 'P')
print 'aaaaaaaaaaa'

the result is 'aaaaaaaa'

IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'SpAldinDoors' AND type = 'P')
print 'aaaaaaaaaaa'

nothing happens as expected.
But I want to create procedure ONLY if it not exists, Don't want to drop it first. How to do that?
TIA
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform