Bran-Damage
asked on
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.
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
ASKER
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
**************************
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
>If i run it without the end I get these results:
I assume you meant without the 'then'
try taking the 'go's out
I assume you meant without the 'then'
try taking the 'go's out
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
Excellent, that worked perfectly.
Thanks!
Thanks!
thanks
Open in new window