Varshini S
asked on
how to stop or break execution of a SQL script ?
i have the following script and i saved as SQL script file. I am executing this script every time in my client production db during migration. All my script files i have check condition in the top. If the condition is successful it should not execute this file and it should break the execution and it need to say 'Invalid database'.
But when i execute this script the if condition in the top is executing with 'Invalid database' message. But the script does not break and it is executing all the below insert table commands. How to stop this ?
If it is invalid database below lines should not execute. How to prevent this ?
But when i execute this script the if condition in the top is executing with 'Invalid database' message. But the script does not break and it is executing all the below insert table commands. How to stop this ?
If it is invalid database below lines should not execute. How to prevent this ?
IF NOT EXISTS(SELECT * FROM configTable where configValue like 'SVS1' ))
BEGIN
raiserror('Invalid database', 15, 10)
RETURN
END
insert into table a (name,age) values ('aaa',20)
go
insert into table a (name,age) values ('aaaa',21)
go
insert into table a (name,age) values ('bb',21)
go
ASKER
I have insert, update and create storeprocedure commands. Is it possible to use begin and end below that ?
RETURN command is only for exit from a stored procedure. As Lee said, you should use a ELSE block:
IF NOT EXISTS(SELECT * FROM configTable where configValue like 'SVS1' ))
raiserror('Invalid database', 15, 10)
ELSE
BEGIN
insert into table a (name,age) values ('aaa',20)
go
insert into table a (name,age) values ('aaaa',21)
go
insert into table a (name,age) values ('bb',21)
go
END
If there is far more to this script than the above, I will need to see all of it to understand the larger problem. Without proper context, helping you will be very difficult. What are you trying to achieve? Is this part of a stored procedure?
ASKER
Hi Vitor,
In my script file i have insert and create command. So when i tried if else block it showing error in the end.
In my script file i have insert and create command. So when i tried if else block it showing error in the end.
Varshini, you are only showing us part of the code. We are experts but guessing is not YET part of our capabilities.
Can you post full script so we may help you better?
Can you post full script so we may help you better?
You probably want something like:
IF NOT EXISTS(SELECT * FROM configTable where configValue like 'SVS1' ))
BEGIN
raiserror('Invalid database', 15, 10)
END
ELSE
BEGIN
insert into table a (name,age) values ('aaa',20)
go
insert into table a (name,age) values ('aaaa',21)
go
insert into table a (name,age) values ('bb',21)
go
END
ASKER
It is not stored procedure.
It has insert commands and alter commands and create store procedure and create table commands.
It has insert commands and alter commands and create store procedure and create table commands.
ASKER
i added the if condition but it showing the following error message
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'END'.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'end'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'END'.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'end'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Assuming you have sysadmin authority, and you really want to immediately stop all processing on that connection, raise the error level and SQL will automatically disconnect the process after sending you the error message:
raiserror('Invalid database', 20, 10) with log
You shouldn't need a RETURN or anything else, the "20"+ level error alone should kill the session.
raiserror('Invalid database', 20, 10) with log
You shouldn't need a RETURN or anything else, the "20"+ level error alone should kill the session.
ASKER
I have removed the GO. Now it is working fine.
Removing Go won't give any issue . Am I correct ?
Removing Go won't give any issue . Am I correct ?
ASKER
Vitor: This is my complete script file
IF NOT EXISTS(SELECT * FROM Table1 WHERE snumber LIKE 'a.3.%' )
BEGIN
RAISERROR('Unable to execute', 11, 1)
RETURN
END
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[gt_starttime]') AND parent_object_id = OBJECT_ID(N'[dbo].[ssFile'))
BEGIN
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[gt_starttime]') AND parent_object_id = OBJECT_ID(N'[dbo].[ssFile']'))
ALTER TABLE [dbo].[ssFile'] DROP CONSTRAINT [gt_starttime]
END
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[endtime_gt_starttime]') AND parent_object_id = OBJECT_ID(N'[dbo].[ssFile]'))
ALTER TABLE [dbo].[ssFile] DROP CONSTRAINT [endtime_gt_starttime]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ssFile]') AND type in (N'U'))
DROP TABLE [dbo].[ssFile]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ssFile]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ssFile](
[SSId] [smallint] NOT NULL,
[TCode] [char](20) NOT NULL,
[TMode] [char](1) NOT NULL,
[RRCode] [char](5) NOT NULL,
[FileName] [varchar(50) ,
[Pages] [int],
CONSTRAINT [PK_ssFile] PRIMARY KEY CLUSTERED
(
[SSId] ASC,
[TCode] ASC,
[TMode] ASC,
[RRCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[gt_starttime]') AND parent_object_id = OBJECT_ID(N'[dbo].[ssFile]'))
ALTER TABLE [dbo].[ssFile] WITH CHECK ADD CONSTRAINT [gt_starttime] CHECK (([endtime]>dateadd(minute,(90),[starttime])))
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[gt_starttime]') AND parent_object_id = OBJECT_ID(N'[dbo].[ssFile]'))
ALTER TABLE [dbo].[ssFile] CHECK CONSTRAINT [gt_starttime]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSList]') AND type in (N'U'))
DROP TABLE [dbo].[SSList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
GO
Removing Go won't give any issue . Am I correct ?Yes. GO is a batch termination and that's why when you used it the engine complained about the END because after the GO the block BEGIN...END was broke.
If...
begin
...
end
else
begin
inserts here
end