Avatar of Varshini S
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 ?




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

Open in new window

Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Lee

You could use:

If...
begin
...
end
else
begin
   inserts here
end
ASKER
Varshini S

I have insert, update and create storeprocedure commands. Is it possible to use begin and end below that ?
Vitor Montalvão

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

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Lee

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
Varshini S

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.
Vitor Montalvão

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Richard Obenchain

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

Open in new window

ASKER
Varshini S

It is not stored procedure.
It has insert commands and alter commands and create store procedure and create table commands.
ASKER
Varshini S

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'.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Vitor Montalvão

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott Pletcher

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.
ASKER
Varshini S

I have removed the GO. Now it is working fine.  
Removing Go won't give any issue . Am I correct ?
ASKER
Varshini S

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

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Vitor Montalvão

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.