CODEWITHSUNDEEP
sql-servert-sqlif-statementexists
panki
panki

Reputation: 9

If NOT EXISTS in SQL not function

I put this in the SQL Query editor of SQL Server 2008 and doesn't work:

If not EXISTS (Select * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= 'dbo' AND TABLE_NAME='PK01')
    CREATE TABLE [dbo].['PK01'] (Articulo varchar(MAX),Referencia varchar(MAX),Lote varchar(MAX),Cantidad Int,Ubicacion varchar(MAX));
    INSERT INTO [UBICACIONES].[dbo].['PK01'] (Articulo,Referencia,Lote,Cantidad,Ubicacion)VALUES ('998','kk','ll',0,'pp')
else
    UPDATE [UBICACIONES].[dbo].['PK01'] Set Cantidad = (Cantidad + 23) WHERE Articulo LIKE '998'

Error I am getting:

Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'else'.

Upvotes: 1

Views: 18647

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This is highly not desirable. You should really create tables before you use them. But, you can do this using dynamic SQL:

DECLARE @sql NVARCHAR(MAX);

If not EXISTS (Select * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'PK01')
BEGIN
    SET @sql = 'CREATE TABLE [dbo].[''PK01''] (Articulo varchar(MAX),Referencia varchar(MAX),Lote varchar(MAX),Cantidad Int,Ubicacion varchar(MAX))';
    EXEC sp_executesql @sql;

    SET @sql = '
    INSERT INTO [UBICACIONES].[dbo].[''PK01''] (Articulo,Referencia,Lote,Cantidad,Ubicacion)VALUES (''998'',''kk'',''ll'',0,''pp'')';
    EXEC sp_executesql @sql;
END;
else
BEGIN
    SET @sql = '
    UPDATE [UBICACIONES].[dbo].[''PK01''] Set Cantidad = (Cantidad + 23) WHERE Articulo LIKE ''998''  '  ;

    EXEC sp_executesql @sql;     
END;

Having done all that, I'll repeat. This seems like a really bad application design.

Upvotes: 0

SteveB
SteveB

Reputation: 799

Your if statement only acts on the first statement after the IF unless you put a BEGIN/END around the statements. This is the reason I like to have BEGIN/ENDs around even one statement IFs.

If not EXISTS (Select * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= 'dbo' AND TABLE_NAME='PK01')
BEGIN
    CREATE TABLE [dbo].['PK01'] (Articulo varchar(MAX),Referencia varchar(MAX),Lote varchar(MAX),Cantidad Int,Ubicacion varchar(MAX));
    INSERT INTO [UBICACIONES].[dbo].['PK01'] (Articulo,Referencia,Lote,Cantidad,Ubicacion)VALUES ('998','kk','ll',0,'pp')
END
ELSE
BEGIN
    UPDATE [UBICACIONES].[dbo].['PK01'] Set Cantidad = (Cantidad + 23) WHERE Articulo LIKE '998'         
END

Upvotes: 5

Related Questions