You posted this question in the SQL 2000 forum, so I don't know if you can use this.

If you are on SQL version 2005+ then you have the ability to use a TRY...CATCH block. If you alter the procedure like the code below, the error message is catched and won't be displayed. The procedure continues without errors.

ALTER PROCEDURE p1

AS

DECLARE @TestTable TABLE (ID INT, NAME NVARCHAR(100))

INSERT INTO @TestTable

([ID], [NAME])

VALUES(1, -- ID - int

N'Omid' -- NAME - nvarchar(100)

)

INSERT INTO @TestTable

([ID], [NAME])

VALUES(2, -- ID - int

N'Saeed' -- NAME - nvarchar(100)

)

DECLARE @ID INT, @Name NVARCHAR(100)

WHILE EXISTS (SELECT 1 FROM @TestTable)

BEGIN

BEGIN TRAN User_Tran

SELECT TOP 1

@ID = [ID],

@Name = Name

FROM@TestTable

begin try-- try catch block added

INSERT INTO TestTable

( ID, Name)

VALUES ( @ID, @Name)

end try-- try catch block added

begin catch-- try catch block added

end catch-- try catch block added

DELETE FROM @TestTable WHERE ID = @ID

if @@trancount > 0-- only commit transaction when there is an open transaction

COMMIT TRANSACTION User_Tran

END

GO