Error Handling in sp_ExecuteSQL

  • 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.

    ---

  • 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

  • Thanks MITHUN

  • 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