Solved

how to  stop or break execution of a SQL script ?

Posted on 2014-12-03
14
95 Views
Last Modified: 2015-01-27
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

0
Comment
Question by:Varshini S
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40478738
You could use:

If...
begin
...
end
else
begin
   inserts here
end
0
 

Author Comment

by:Varshini S
ID: 40478762
I have insert, update and create storeprocedure commands. Is it possible to use begin and end below that ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40478767
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

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40478768
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?
0
 

Author Comment

by:Varshini S
ID: 40478772
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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40478781
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?
0
 
LVL 3

Expert Comment

by:Richard Obenchain
ID: 40478797
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

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Varshini S
ID: 40478802
It is not stored procedure.
It has insert commands and alter commands and create store procedure and create table commands.
0
 

Author Comment

by:Varshini S
ID: 40478806
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'.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40478815
I see. Forgot to take the GO's:
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)
    insert into table a  (name,age) values ('aaaa',21)
    insert into table a  (name,age) values ('bb',21)
END

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40478940
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.
0
 

Author Comment

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

Author Comment

by:Varshini S
ID: 40479158
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

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40480224
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now