June 8, 2009 at 5:59 am
HI All,
DECLARE@SQLNVARCHAR(512)
DECLARE@MaxCountINT
DECLARE@CountINT
DECLARE@FieldName NVARCHAR(512)
DECLARE @DBName TABLE (fldID int IDENTITY(1,1), fldDBName varchar(512))
INSERT INTO @DBName (fldDBName)
SELECT 'Test1' UNION ALL
SELECT 'Test2' UNION ALL
SELECT 'Test3'
SELECT @MaxCount = COUNT(fldID) FROM @DBName
SET @Count = 1
WHILE @Count <= @MaxCount
BEGIN
SELECT @FieldName = fldDBName FROM @DBName WHERE fldID = @Count
SET @SQL = N'USE [MASTER]' + CHAR(13) + 'DROP DATABASE ' + '[' + @FieldName + ']'
EXEC sp_ExecuteSQL @SQL /* <-- (Any Error Encounted) */SET @Count = @Count + 1
END
How to handle error(s) while using sp_ExecuteSQL??
For Ex(Pl see the above example), in this if any error encounted (while executing EXEC sp_ExecuteSQL @SQL), how can i
handle the error.
Appreciate your help.
---
June 8, 2009 at 7:42 am
MAY BE THIS CAN HELP..
DECLARE @SQL NVARCHAR(512)
DECLARE @MaxCount INT
DECLARE @Count INT
DECLARE @FieldName NVARCHAR(512)
DECLARE @DBName TABLE (fldID int IDENTITY(1,1), fldDBName varchar(512))
INSERT INTO @DBName (fldDBName)
SELECT 'Test1' UNION ALL
SELECT 'Test2' UNION ALL
SELECT 'Test3'
SELECT @MaxCount = COUNT(fldID) FROM @DBName
SET @Count = 1
WHILE @Count <= @MaxCount
BEGIN
SELECT @FieldName = fldDBName FROM @DBName WHERE fldID = @Count
SET @SQL = N'USE [MASTER]' + CHAR(13) + 'DROP DATABASE ' + '[' + @FieldName + ']'
BEGIN TRY
EXEC sp_ExecuteSQL @SQL /* <-- (Any Error Encounted) */
END TRY
BEGIN CATCH
RAISERROR('DONT HAVE THESE DBS OR NO PERMISSIONS',10,1)
END CATCH
SET @Count = @Count + 1
END
MITHUN
June 8, 2009 at 11:53 pm
Thanks MITHUN
March 8, 2011 at 8:21 am
Worked GREAT !!! THANKS
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply