Stored Procedure Error: If Statement Madness!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure Error: If Statement Madness!

Author

Topic

abrahamlloyd
Starting Member

3 Posts

Posted - 2005-12-23 : 13:21:06

Good day. I hope everything is going well for you.

I am working on a procedure to create a backup device as part of a larger solution to implement log shipping. For whatever reason, my 'create_backup_device' procedure is not respecting the return code being returned by sp_addumpdevice.

In the procedure, I capture the return code from sp_addumpdevice, and then check to see if an error occurred (value > 0). If so, I define the error properties, and exit the procedure. What is happening, however, is that the procedure is always processing the error traping code (regardless of whether the device was added or not).

This is being developed against SQL 2000 SP4. I have been dealing with this issue for a couple of days now, and don't really understand why its occurring. Any help would be greatly appreciated.

Best Wishes and Regards,

Abraham Lloyd
abraham@onproject.com


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].stp_database_create_backup_device') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].stp_database_create_backup_device
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

--
-- Create Database Backup Device
-- ------------------------------------------
-- This procedure is used to create the backup device for database proceeding 
-- with the automated backup.  This procedure is based on the scripts originally
-- written by Chris Kempster (http://www.sqlservercentral.com/columnists/ckempster/customlogshipping.asp)
--
-- Revision History
-------------------------------------------------------------------
-- Initials		Date		Description
-- 12/21/2005	ADL			Created.

-- Build the procedure used to manage database backups (create a backup device)
create procedure dbo.stp_database_create_backup_device

-- Define the procedure parameters
@errorMessage as varchar(1000) = null output,	-- Identifies any error messages returned by this procedure
@database as varchar(250),			-- Identifies the name of the database being backed up
@backupDevice as varchar(500),			-- Describes the backup device that will be create
@fileName as varchar(500)			-- Describes the file name of the backup device that will be created

as 
begin

	-- Disable network messages/recordcounts
	set nocount on

	-- Initialize any variables required by this procedure
	declare @l_status int			-- Working variable used to describe if an error occurred
	declare @l_returnValue int		-- Variable used to store the return value for this procedure

	-- Default the return value
	set @l_returnValue = 1
	set @l_status = 0

	-- Add the dump device to the sql server
	exec @l_status = master..sp_addumpdevice 
	@devType='disk', 
	@logicalName=@backupDevice, 
	@physicalName=@fileName

	-- Did an error occur? Goto the error handler
	if @l_status > 0
	begin
	
		-- Set the return value to -1 (indicates an error occurred)
		set @l_returnValue = -1

		-- Set the output error message
		set @errorMessage = 'Failed to create dump device: [' + @backupDevice + '] at [' + @fileName + '].  Backup of database [' + @database + '] failed.'

	end

-- Complete script logic
end

-- Return the return value of this procedure to the caller.
return @l_returnValue

GO

-- Execute the procedure for testing purposes
declare @errorMessage varchar(1000)
declare	@returnCode int

exec @returnCode = dbo.stp_database_create_backup_device
@errorMessage = @errorMessage output,			-- Identifies any error messages returned by this procedure
@database = 'Northwind',				-- Identifies the name of the database being backed up
@backupDevice = 'NorthWind_Testing',			-- Describes the name of the backup device being created
@fileName = 'c:\backups\northwind_test.bak'		-- Describes the file name of the backup device being created

-- Output the return code and error message
select	@returnCode,
	@errorMessage

abrahamlloyd
Starting Member

3 Posts

Posted - 2005-12-23 : 13:36:58

Sorry, I wanted to include code to drop the testing device for people that actually wanted to execute this procedure. You can use this code to drop the device referenced in the exec statement found in the previous post. Thanks in advance for your help. I look forward to hearing from you.

Best Wishes and Regards,

Abraham Lloyd
abraham@onproject.com


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].stp_database_drop_backup_device') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].stp_database_drop_backup_device
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

--
-- Drop Database Backup Device
-- ------------------------------------------
-- This procedure is used to drop the backup device that pre-exists for database proceeding 
-- with the automated backup.  This procedure is based on the scripts originally
-- written by Chris Kempster (http://www.sqlservercentral.com/columnists/ckempster/customlogshipping.asp)
--
-- Revision History
-------------------------------------------------------------------
-- Initials		Date		Description
-- 12/21/2005	ADL			Created.

-- Build the procedure used to manage database backups (validate database properties)
create procedure dbo.stp_database_drop_backup_device

-- Define the procedure parameters
@errorMessage as varchar(1000) = null output,	-- Identifies any error messages returned by this procedure
@database as varchar(200),			-- Identifies the name of the database being backed up
@backupDevice as varchar(500)			-- Describes the backup device that will be dropped

as 
begin

	-- Disable network messages/recordcounts
	set nocount on

	-- Initialize any variables required by this prcoedure
	declare @l_error integer		-- Working variable used to describe if an error occurred
	declare @l_returnValue integer		-- Variable used to store the return value for this procedure
	declare @l_errorMessage varchar(4000)	-- Working variable used to store error messages

	-- Default the return value
	set @l_returnValue = 1

	-- Default the error value
	set @l_error = 0

	-- Determine if the backup device already exists; if it does then drop the device
	-- Was the specified backup device found?  If so, then drop the original backup device
	if exists( select 1 from master..sysdevices where name = @backupDevice )

		-- Drop the found backup device, since it will be added again
		exec @l_error = master..sp_dropdevice @backupDevice

	-- Was an error captured? Goto the error handler
	if @l_error <> 0
	begin

		-- Set the return value; indicate an error occurred
		set @l_returnValue = -1

		-- Set the output error message
		set @ErrorMessage = 'Failed to drop existing dump device: ' + @backupDevice + '.  Backup of database [' + @database + '] failed.'
	
	end

-- Complete script logic
end

-- Return the return value of this procedure to the caller.
return @l_returnValue

GO

-- Execute the procedure for testing purposes
declare @errorMessage varchar(1000)
declare	@returnCode int

exec @returnCode = dbo.stp_database_drop_backup_device
@errorMessage = @errorMessage output,			-- Identifies any error messages returned by this procedure
@database = 'Northwind',				-- Identifies the name of the database being backed up
@backupDevice = 'NorthWind_Testing'			-- Describes the name of the backup device being dropped

-- Output the return code and error message
select	@returnCode,
	@errorMessage

abrahamlloyd
Starting Member

3 Posts

Posted - 2005-12-23 : 13:41:38

Ok -- my last post. ;-)

On the top of "If Statement Madness", the procedure below (used to add a dump device) will not compile since I added the if/else block. The if/else conditions contain separate begin/end statements. Since I added the else/begin/end statements, the procedure will not compile and SQL Server returns the following error:

Server: Msg 156, Level 15, State 1, Procedure stp_database_create_backup_device, Line 51
Incorrect syntax near the keyword 'else'.

I do not understand why adding the else/begin/end would cause this error to occur, as I have used nested if/begin/end/else/begin/end statements in the past. Does anyone know why this could happen, or what I am missing?

As always, thank you in advance for help. I look forward to hearing from you.

Best Wishes and Regards,

Abraham Lloyd
abraham@onproject.com



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].stp_database_create_backup_device') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].stp_database_create_backup_device
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

--
-- Create Database Backup Device
-- ------------------------------------------
-- This procedure is used to create the backup device for database proceeding 
-- with the automated backup.  This procedure is based on the scripts originally
-- written by Chris Kempster (http://www.sqlservercentral.com/columnists/ckempster/customlogshipping.asp)
--
-- Revision History
-------------------------------------------------------------------
-- Initials		Date		Description
-- 12/21/2005	ADL			Created.

-- Build the procedure used to manage database backups (create a backup device)
create procedure dbo.stp_database_create_backup_device

-- Define the procedure parameters
@errorMessage as varchar(1000) = null output,	-- Identifies any error messages returned by this procedure
@database as varchar(250),			-- Identifies the name of the database being backed up
@backupDevice as varchar(500),			-- Describes the backup device that will be create
@fileName as varchar(500)			-- Describes the file name of the backup device that will be created

as 
begin

	-- Disable network messages/recordcounts
	set nocount on

	-- Initialize any variables required by this procedure
	declare @l_status int			-- Working variable used to describe if an error occurred
	declare @l_returnValue int		-- Variable used to store the return value for this procedure

	-- Default the return value
	set @l_returnValue = 1
	set @l_status = 0

	-- Add the dump device to the sql server
	exec @l_status = master..sp_addumpdevice 
	@devType='disk', 
	@logicalName=@backupDevice, 
	@physicalName=@fileName

	-- Did an error occur? Goto the error handler
	if @l_status > 0
	begin
	
		-- Set the return value to -1 (indicates an error occurred)
		set @l_returnValue = -1

		-- Set the output error message
		set @errorMessage = 'Failed to create dump device: [' + @backupDevice + '] at [' + @fileName + '].  Backup of database [' + @database + '] failed.'

	end
	else
	begin

		-- Otherwise, alter the user that no error occurred
		print 'No error occurred!'

	end

-- Complete script logic
end

-- Return the return value of this procedure to the caller.
return @l_returnValue

GO

-- Execute the procedure for testing purposes
declare @errorMessage varchar(1000)
declare	@returnCode int

exec @returnCode = dbo.stp_database_create_backup_device
@errorMessage = @errorMessage output,			-- Identifies any error messages returned by this procedure
@database = 'Northwind',				-- Identifies the name of the database being backed up
@backupDevice = 'NorthWind_Testing',			-- Describes the name of the backup device being created
@fileName = 'c:\backups\northwind_test.bak'		-- Describes the file name of the backup device being created

-- Output the return code and error message
select	@returnCode,
	@errorMessage

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources