Solved

how to  stop or break execution of a SQL script ?

Posted on 2014-12-03
14
104 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 46

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 46

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 46

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 46

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql user function 7 31
SQL Query 2 43
Can Unique column have more than one Null? 8 43
T SQL Update Table from another table 5 40
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
A short film showing how OnPage and Connectwise integration works.

947 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

20 Experts available now in Live!

Get 1:1 Help Now