Hey guys i need your help, i am trying to generate custom sql error messages, but its not working very wull.
There are 2 custom generated messages ErrorMsg and ErrorMsg1, ErrorMsg workes fine, but ErrorMsg1 doesn't. Somehow it keep getting overwritten by the default sql error message.
And sorry about the sql, the space allotted is not sufficient for proper spacing of the sql.
USE [AssetTracker]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Populate_Asset_Table]
@AssetIdentifier varchar(50),
@AssetCondition int,
@SchoolID int,
@AssetCategoryID int,
@VendorID int,
@AssingedPersonID int,
@AssetStatusID int,
@ManufacturerID int,
@ModelDetail varchar(50),
@CreatedOn datetime,
@Notes varchar(max)
AS
DECLARE @CreatedByIdentifier nvarchar(50),@ModifiedByIdentifier nvarchar(50),@ModifiedOn datetime,@ErrorMsg nvarchar(500)
SET @CreatedByIdentifier =(SELECT SUSER_NAME())
SET @ModifiedByIdentifier =(SELECT SUSER_NAME())
SET @ModifiedOn = (SELECT GETDATE())
SET @ErrorMsg = 'The Asset with this identifier:'+@AssetIdentifier +',already exists in this table.'
BEGIN
IF EXISTS(SELECT AssetIdentifier FROM AssetTracker_Asset WHERE AssetIdentifier = @AssetIdentifier)
BEGIN
RAISERROR(@ErrorMsg, 10, 1)
RETURN
END
ELSE
BEGIN
BEGIN TRY
INSERT INTO AssetTracker_Asset VALUES(@AssetIdentifier,@AssetCondition,@SchoolID,@AssetCategoryID,@VendorID,@AssingedPersonID,
@AssetStatusID,@ManufacturerID,@ModelDetail,@CreatedOn,@CreatedByIdentifier,@ModifiedOn,@ModifiedByIdentifier,@Notes)
END TRY
BEGIN CATCH
DECLARE @ErrorNum int, @ErrorMessage nvarchar(4000), @ErrorMsg1 nvarchar(500)
SELECT @ErrorNum = ERROR_NUMBER()
SELECT @ErrorMessage = ERROR_MESSAGE()
SET @ErrorMsg1 ='Error Number:'+@ErrorNum + 'And Error Message:'+@ErrorMessage +'. Occured in Populate_Asset_Table while inserting these elments'
RAISERROR (@ErrorMsg1,10,1)
RETURN
END CATCH
END
END
Thanks for your help