How to exit the Azure SQL script to skip all remaining batches?
0
votes
3
answers
119
views
I have a Python script that glues together individual SQL scripts like: creating tables, creating views, creating functions, creating stored procedures... It produces the single installation SQL script.
Having more "similar" databases in one SQL server and using SSMS, I would like to avoid the user error by using the installation script for the unrelated database.
The Python script basically just concatenates the individual scripts in the correct order with the
GO
command after each one. Like this:
-- create_table_log.sql --------------------
IF (OBJECT_ID(N'dbo.log', 'U') IS NULL) BEGIN
CREATE TABLE dbo.log (
ts datetime2 NOT NULL,
user_ nvarchar(40) NOT NULL,
msg nvarchar(MAX) NOT NULL,
code int NOT NULL
)
END
GO
-------------------------------------------
-- usp_log.sql --------------------
IF (OBJECT_ID(N'dbo.usp_log', 'P') IS NOT NULL)
DROP PROCEDURE dbo.usp_log
GO
CREATE PROCEDURE dbo.usp_log @msg nvarchar(MAX),
@code int = 0,
@user nvarchar(40) = ''
AS
BEGIN
SET NOCOUNT ON
IF @user = ''
SET @user = SYSTEM_USER
INSERT dbo.log (ts, user_, msg, code)
VALUES (SYSDATETIME(), @user, @msg, @code)
PRINT @msg
END
My idea was to add the initial part that would check the name of the database and would exit the whole script early in the case. So, I have used:
DECLARE @expected_dbname nvarchar(50) = 'xxx_db'
DECLARE @this_dbname nvarchar(50) = DB_NAME()
IF @this_dbname @expected_dbname BEGIN
RAISERROR(N'Must be executed in the context of the ''%s'' database.', 0, 1, @expected_dbname) WITH NOWAIT
RAISERROR(N'Was executed in the context of ''%s'' (early termination).', 11, 1, @this_dbname) WITH NOWAIT
RETURN
END
GO
-------------------------------------------
The RETURN
ends execution only of the current batch. However, I was expecting that RAISERROR
with severity 11 (*Indicates that the given object or entity doesn't exist.*) will end the execution in the SSMS console. It did not.
**What is the correct way to stop execution immediately -- not executing even further batches in the same script?** As the batches (between GO
) are executed kind of independently, it probably has to be the equivalent of the "cancel button" in the SSMS.
Asked by pepr
(143 rep)
Jan 30, 2024, 01:16 PM
Last activity: Jan 31, 2024, 06:35 PM
Last activity: Jan 31, 2024, 06:35 PM