In 2003 and 2004, when this news letter was the
UDF of the Week Newsletter,
it featured the occasional user contributed code. The format
of Coding-in-SQL doesn't really lend itself to most user
contributions but shen a reader, Jason Schaitel, sent in some code
last week I decided that I should find some way to publish it.
So I'm sending out this Bonus Issue.
The stored procedure below, usp_DeleteIfExists,
deletes a SQL object by first testing if it exists and if it does,
deleting it. This is a frequently written task and having a
stored procedure that handles it is a great convenience.
Here's the procedure:
CREATE PROCEDURE
usp_DeleteIfExists
/****************************************************************
*****************************************************************
** NAME: usp_DeleteIfExists
** DATE: 8/1/2003
** AUTHOR: JASON SCHAITEL
** SPONSOR: Verne Claussen
** SCOPE: LOCAL
** PROCESS: N/A
** TYPE: USER STORED PROCEDURE
** PURPOSE: Pass in an object type
and object name. If the object
** exists it will be deleted or
dropped
**
** EXAMPLE:
EXEC usp_DeleteIfExists 'Customers'
-- defaults table
or
EXEC usp_DeleteIfExists '#BASE',
'TEMP TABLE'
or
EXEC usp_DeleteIfExists 'vw_Customers',
'VIEW'
or
EXEC usp_DeleteIfExists 'usp_GetCustomers',
'STORED PROC'
or
EXEC usp_DeleteIfExists 'IsBigSpendingCustomers',
'UDF'
or
EXEC usp_DeleteIfExists 'IDX_CustomerID',
'INDEX', 'Customers'
or
EXEC usp_DeleteIfExists 'MyTrigger',
'TRIGGER'
*****************************************************************
****************************************************************/
/****************************************************************
** BEGIN - CREATE STORED PROCEDURE
- usp_DeleteIfExists
**
** INPUT: @ObjectName: pass in a
name of an object you are trying
to delete
@ObjectType: Defaults to
"TABLE" but other valid values are -
"INDEX", "VIEW",
"STORED PROC", "STORED PROCEDURE", "PROC",
"PROCEDURE", "UDF",
"USER DEFINED FUNCTION", "FUNCTION",
"TRIGGER"
@IndexTableName: name of table
index you are trying to delete
is on
**
** OUTPUT: NONE
**
*****************************************************************/
@ObjectName nvarchar(255)
,@ObjectType nvarchar(255) =
'TABLE'
,@IndexTableName nvarchar(255)
= NULL
AS
DECLARE @SQL
NVARCHAR(4000)
DECLARE @ISERROR
TINYINT
DECLARE @EMsg
VARCHAR(255)
SET @SQL = ''
SET @ISERROR = 0
IF @ObjectType = 'TABLE'
BEGIN
SET @SQL = N'IF
EXISTS(SELECT name FROM sysobjects WHERE '
+ 'name = N'''
+ @ObjectName + ''' AND type = ''U'')'+CHAR(13)
SET @SQL = @SQL +
N'DROP TABLE ' + @ObjectName
SET @ISERROR = 1
END
IF @ObjectType = 'TEMP
TABLE'
BEGIN
SET @SQL = N'IF OBJECT_ID(
''tempdb..' + @ObjectName
+ ''' ) IS NOT NULL DROP TABLE ' + @ObjectName
+ ''
SET @ISERROR = 1
END
IF @ObjectType = 'INDEX'
BEGIN
IF @IndexTableName
IS NOT NULL
BEGIN
-- trap for error 3703 "Can't
drop index, table does not exist"
IF EXISTS(SELECT
name FROM sysobjects
WHERE
name=@IndexTableName AND
type='U')
BEGIN
IF EXISTS(SELECT
name FROM sysindexes WHERE name=@ObjectName)
BEGIN
SET @SQL = N'DROP
INDEX ' + @IndexTableName + '.'
+ @ObjectName
SET @ISERROR = 1
END
ELSE
BEGIN
SET @ISERROR = 1
END
END
ELSE
BEGIN
SET @ISERROR = 2
END
END
ELSE
BEGIN
SET @ISERROR = 2
END
END
IF @ObjectType = 'VIEW'
BEGIN
SET @SQL = N'IF EXISTS
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS '
+ 'WHERE TABLE_NAME = N''' + @ObjectName +
''')'+ CHAR(13)
SET @SQL = @SQL +
N'DROP VIEW ' + @ObjectName
SET @ISERROR = 1
END
IF @ObjectType = 'PROC'
OR @ObjectType = 'PROCEDURE'
OR @ObjectType
= 'STORED PROC'
OR @ObjectType
= 'STORED PROCEDURE'
BEGIN
SET @SQL = N'IF EXISTS
(SELECT name FROM sysobjects WHERE name = N'''
+ @ObjectName + ''' AND type = ''P'')'+
CHAR(13)
SET @SQL = @SQL +
N'DROP PROCEDURE ' + @ObjectName
SET @ISERROR = 1
END
IF @ObjectType = 'UDF'
OR @ObjectType = 'USER DEFINED
FUNCTION'
OR @ObjectType
= 'FUNCTION'
BEGIN
SET @SQL = N'IF EXISTS
(SELECT * FROM sysobjects WHERE name = N'''
+ @ObjectName + ''')'+
CHAR(13)
SET @SQL = @SQL +
N'DROP FUNCTION ' + @ObjectName
SET @ISERROR = 1
END
IF @ObjectType = 'TRIGGER'
BEGIN
SET @SQL = N'IF EXISTS
(SELECT name FROM sysobjects WHERE name = N'''
+ @ObjectName + ''' AND type = ''TR'')'+
CHAR(13)
SET @SQL = @SQL +
N'DROP TRIGGER ' + @ObjectName
SET @ISERROR = 1
END
IF @ISERROR = 2
BEGIN
SET @EMsg = 'A valid table
name was not passed in for @IndexTableName. '
+ 'An index cannot be dropped if the procedure
does not '
+ 'know the name of table the index is on'
RAISERROR (@EMsg, 11, 1)
RETURN
END
IF @ISERROR = 0
BEGIN
SET @EMsg = 'A valid @ObjectType
was not passed into the procedure. '
+ '@ObjectType Defaults to "TABLE" but other
valid '
+ 'values are: "INDEX", "VIEW", "STORED
PROC", '
+ '"STORED PROCEDURE", "PROC", "PROCEDURE", "UDF",
'
+ '"USER DEFINED FUNCTION", "FUNCTION",
"TRIGGER" '
RAISERROR (@EMsg , 11, 1)
RETURN
END
IF @ISERROR = 1
BEGIN
EXEC sp_executesql @SQL
--SELECT @SQL
IF (@@ERROR <> 0)
BEGIN
PRINT @@Error
-- = @@ERROR
PRINT 'ERROR NUMBER = ' +
CAST(@@ERROR
AS VARCHAR(10))
END
RETURN
END
/***************************************************************************
** END - CREATE STORED PROCEDURE -
usp_DeleteIfExists *********************/
Thank you for the
contribution to Jason.
Do you have an interesting stored procedure, user-defined-function,
or other sample code? If you'd like to share them please send
them in an email to:
coding-in-sql@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andy
Andrew Novick
