Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to  stop or break execution of a SQL script ?

Posted on 2014-12-03
14
Medium Priority
?
149 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 52

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 52

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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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 70

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 52

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

Stack Overflow Podcast - Developer Story

Welcome to the Stack Overflow podcast recorded Thursday July 20 at Stack Overflow Headquearters in NYC. Your hosts today are podcast regulars Jay Hanlon, David Fullerton, and Ilana Yitzhaki, plus the quite irregular Matt Sherman (Stack Overflow Engineering Manager extraordinaire)

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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