Use an if then or a case statement in sql

I have a stored procedure that checks whether a table exists or not.

I then want to take the results and do a check on it, if it = 0 then insert the table, if it = 1 then do not insert the table.

The execution of the stp works fine.  I am getting an incorrect syntax near the word then when running the query.
declare @Results integer
exec sp_CheckIfTableExists 'ErpOdbc', @Results OUTPUT
Print 'The results of the sp_checkiftableexists was - ' + str(@Results)
if @Results = 0 then
begin 
	BEGIN TRANSACTION
	SET QUOTED_IDENTIFIER ON
	SET ARITHABORT ON
	SET NUMERIC_ROUNDABORT OFF
	SET CONCAT_NULL_YIELDS_NULL ON
	SET ANSI_NULLS ON
	SET ANSI_PADDING ON
	SET ANSI_WARNINGS ON
	COMMIT
	BEGIN TRANSACTION
	GO
	CREATE TABLE dbo.ErpOdbc
		(
		Interface nvarchar(MAX) NULL,
		DSN nvarchar(50) NULL,
		Server nvarchar(50) NULL,
		DatabaseName nvarchar(MAX) NULL,
		UserID nvarchar(50) NULL,
		Password nvarchar(50) NULL,
		Plant nvarchar(50) NULL
		)  ON [PRIMARY]
		TEXTIMAGE_ON [PRIMARY]
	GO
	COMMIT
	print '************************************************'
	print 'ErpOdbc table has been inserted successfully'
	print '************************************************'
end
else
begin
	print '************************************************'
	print 'ErpOdbc table has already been inserted'
	print '************************************************'
End
go

Open in new window

Bran-DamageAsked:
Who is Participating?
 
Atdhe NuhiuCommented:
this runs for me

declare @results as int
set @Results = 0
if @Results = 0 
begin 
        BEGIN TRANSACTION
        SET QUOTED_IDENTIFIER ON
        SET ARITHABORT ON
        SET NUMERIC_ROUNDABORT OFF
        SET CONCAT_NULL_YIELDS_NULL ON
        SET ANSI_NULLS ON
        SET ANSI_PADDING ON
        SET ANSI_WARNINGS ON
        COMMIT

        BEGIN TRANSACTION
        CREATE TABLE dbo.ErpOdbc
                (
                Interface nvarchar(MAX) NULL,
                DSN nvarchar(50) NULL,
                Server nvarchar(50) NULL,
                DatabaseName nvarchar(MAX) NULL,
                UserID nvarchar(50) NULL,
                Password nvarchar(50) NULL,
                Plant nvarchar(50) NULL
                )  ON [PRIMARY]
                TEXTIMAGE_ON [PRIMARY]
        COMMIT
        print '************************************************'
        print 'ErpOdbc table has been inserted successfully'
        print '************************************************'
end

Open in new window

0
 
Atdhe NuhiuCommented:
you don't need 'then'

if @Results = 0 
begin 
        BEGIN TRANSACTION
        SET QUOTED_IDENTIFIER ON
        SET ARITHABORT ON
        SET NUMERIC_ROUNDABORT OFF
        SET CONCAT_NULL_YIELDS_NULL ON
        SET ANSI_NULLS ON
        SET ANSI_PADDING ON
        SET ANSI_WARNINGS ON
        COMMIT
        BEGIN TRANSACTION
        GO
        CREATE TABLE dbo.ErpOdbc
                (
                Interface nvarchar(MAX) NULL,
                DSN nvarchar(50) NULL,
                Server nvarchar(50) NULL,
                DatabaseName nvarchar(MAX) NULL,
                UserID nvarchar(50) NULL,
                Password nvarchar(50) NULL,
                Plant nvarchar(50) NULL
                )  ON [PRIMARY]
                TEXTIMAGE_ON [PRIMARY]
        GO
        COMMIT
        print '************************************************'
        print 'ErpOdbc table has been inserted successfully'
        print '************************************************'
end
else
begin
        print '************************************************'
        print 'ErpOdbc table has already been inserted'
        print '************************************************'
End
go

Open in new window

0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Bran-DamageAuthor Commented:
If i run it without the end I get these results:
************************************************
Beginning ErpOdbc table insert
************************************************
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'TRANSACTION'.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'ErpOdbc' in the database.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'else'.

also, I know that the results is going to = 1 in my test case.

If I run just this part:
declare @Results integer
exec sp_CheckIfTableExists 'ErpOdbc', @Results OUTPUT
Print 'The results of the sp_checkiftableexists was - ' + str(@Results)

I get a 1
0
 
Atdhe NuhiuCommented:
>If i run it without the end I get these results:

I assume you meant without the 'then'

try taking the 'go's out
0
 
Atdhe NuhiuCommented:
and so does this

declare @results as int
set @Results = 1
if @Results = 0 
begin 
        BEGIN TRANSACTION
        SET QUOTED_IDENTIFIER ON
        SET ARITHABORT ON
        SET NUMERIC_ROUNDABORT OFF
        SET CONCAT_NULL_YIELDS_NULL ON
        SET ANSI_NULLS ON
        SET ANSI_PADDING ON
        SET ANSI_WARNINGS ON
        COMMIT

        BEGIN TRANSACTION
        CREATE TABLE dbo.ErpOdbc
                (
                Interface nvarchar(MAX) NULL,
                DSN nvarchar(50) NULL,
                Server nvarchar(50) NULL,
                DatabaseName nvarchar(MAX) NULL,
                UserID nvarchar(50) NULL,
                Password nvarchar(50) NULL,
                Plant nvarchar(50) NULL
                )  ON [PRIMARY]
                TEXTIMAGE_ON [PRIMARY]
        COMMIT
        print '************************************************'
        print 'ErpOdbc table has been inserted successfully'
        print '************************************************'
end
else
begin
        print '************************************************'
        print 'ErpOdbc table has already been inserted'
        print '************************************************'
End

Open in new window

0
 
Bran-DamageAuthor Commented:
Excellent, that worked perfectly.

Thanks!
0
 
Atdhe NuhiuCommented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.