生成自定义SQL错误 [英] Generating Custom SQL Errors
本文介绍了生成自定义SQL错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
嘿伙计们我需要你的帮助,我正在尝试生成自定义的sql错误信息,但它的工作效果不好。
有两个自定义生成的消息ErrorMsg和ErrorMsg1,ErrorMsg工作正常,但是ErrorMsg1没有。不知怎的,它一直被默认的sql错误消息覆盖。
抱歉sql,分配的空间不足以正确间隔sql。
USE [AssetTracker]
GO
/ * *****对象:StoredProcedure [dbo]。 [Populate_Asset_Table]脚本日期:11/11/2013 13:17:28 ****** /
SET ANSI_NULLS ON
GO
< span class =code-keyword> SET QUOTED_IDENTIFIER ON
GO
- DROP程序[ dbo]。[Populate_Asset_Table]
ALTER 过程 [dbo]。[Populate_Asset_Table]
@ AssetIdentifier varchar ( 50 ),
@AssetCondition int ,
@ SchoolID int ,
@ AssetCategoryID int ,
< span class =code-sdkkeyword> @ 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 = ' 具有此标识符的资产:' + @ AssetIdentifier + ' < span class =code-string>,已存在于此表中。'
BEGIN
IF EXISTS ( SELECT AssetIdentifier FROM AssetTracker_Asset WHERE AssetIdentifier = @ AssetIden tifier )
BEGIN
RAISERROR ( @ ErrorMsg , 10 , 1 ) - 更改为> 10
返回 - 现在退出
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 = ' 错误号:' + @ ErrorNum + ' 和错误消息:' + @ ErrorMessage + ' 。在Populate_Asset_Table中插入这些元素时
RAISERROR ( @ ErrorMsg1 , 10 , 1 )
返回
END CATCH
END
END
感谢您的帮助
解决方案
连接时需要将varchar转换为nvarchar,如下所示:
SET @ ErrorMsg = ' 具有此标识符的资产:' + CONVERT( NVARCHAR ( 100 ), @资产标识符)+ ' ,已存在于此表中。'
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
/****** Object: StoredProcedure [dbo].[Populate_Asset_Table] Script Date: 11/11/2013 13:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--DROP Procedure [dbo].[Populate_Asset_Table]
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) --change to > 10
RETURN --exit now
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
解决方案
You need to convert varchar to nvarchar when concatenating, like this:
SET @ErrorMsg = 'The Asset with this identifier:'+CONVERT(NVARCHAR(100),@AssetIdentifier)+',already exists in this table.'
这篇关于生成自定义SQL错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文