Sample Header Ad - 728x90

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