Solved

how to  stop or break execution of a SQL script ?

Posted on 2014-12-03
14
110 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 47

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 47

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
 

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 47

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:Scott Pletcher
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 47

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

776 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