USE MASTER
DECLARE @DBNAME SYSNAME
SET @DBNAME = ''

DECLARE @ERRORMESSAGE NVARCHAR(1000)

IF EXISTS(SELECT 1 FROM SYS.DATABASES WHERE DATABASE_ID = DB_ID(@DBNAME) AND IS_BROKER_ENABLED = 0)
BEGIN
	IF EXISTS (SELECT 1 FROM SYS.SYSPROCESSES WHERE DBID = DB_ID(@DBNAME) AND SPID <> @@SPID AND STATUS <> 'background')
	BEGIN
		DECLARE @SPID INT
		
		DECLARE KILLPROCESSES_CURSOR CURSOR FOR 
		SELECT SPID FROM SYS.SYSPROCESSES WHERE DBID = DB_ID(@DBNAME) AND SPID <> @@SPID AND STATUS <> 'background'
		
		OPEN KILLPROCESSES_CURSOR
		FETCH NEXT FROM KILLPROCESSES_CURSOR INTO @SPID
		
		WHILE(@@FETCH_STATUS = 0)
		
		BEGIN
		EXEC ('KILL '+@SPID)
		FETCH NEXT FROM KILLPROCESSES_CURSOR INTO @SPID
		END
		
		CLOSE KILLPROCESSES_CURSOR 
		DEALLOCATE KILLPROCESSES_CURSOR 

	END

	IF EXISTS (SELECT 1 FROM SYS.SYSPROCESSES WHERE DBID = DB_ID(@DBNAME))
	BEGIN
		SET @ERRORMESSAGE = 'The '+@DBNAME+' database is currently in use. Service Broker can''t be enabled at the moment. Please contact the administrator or try later.'
		RAISERROR (@ERRORMESSAGE,18,9);
	END
	ELSE
	BEGIN
		IF NOT EXISTS(SELECT 1 FROM SYS.DATABASES WHERE IS_BROKER_ENABLED = 1)
			EXEC('ALTER DATABASE '+ @DBNAME +' SET ENABLE_BROKER')
		ELSE
			EXEC('ALTER DATABASE '+ @DBNAME +' SET NEW_BROKER')
		SELECT 'The Service Broker is enabled on '+ @DBNAME
	END
END
ELSE
BEGIN
	IF EXISTS(SELECT 1 FROM SYS.DATABASES WHERE DATABASE_ID = DB_ID(@DBNAME) AND IS_BROKER_ENABLED = 1)
		SELECT 'The Service Broker for database '+ @DBNAME +' is already enabled.'
	ELSE
	BEGIN
		SET @ERRORMESSAGE = 'The database '+ @DBNAME +' does not exists.'
		RAISERROR (@ERRORMESSAGE,18,9);
	END
END
