>我有一个存储过程,我在其中插入这样的产品:
BEGIN TRY
-- Insert statements for procedure here
INSERT INTO Product.ProductDetail VALUES (@ProductId,@IsDeleted);
return 1;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
INSERT INTO Exception.SqlTransaction VALUES ('usp_AddProduct', ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(),
ERROR_MESSAGE())
return 0
ROLLBACK TRANSACTION
如您所见,我在 Exception.SqlTransactions 中插入了 sp 和 sql 错误代码的名称。我想做的是使用该插入创建另一个sp并使用EXEC
,如何发送sp的名称?
首先,我创建sqlErrorCodes
SP:
CREATE PROCEDURE SQLErrorCodes
-- Add the parameters for the stored procedure here
@sp_Name varchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO Exception.SqlTransaction VALUES (@sp_Name, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(),
ERROR_MESSAGE())
END
GO
然后变成原来的sp我改了这条线
INSERT INTO Exception.SqlTransaction VALUES ('usp_AddProduct', ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(),
ERROR_MESSAGE())
现在我使用:
EXEC SQLErrorCodes('usp_AddProduct')
但我得到:
"usp_AddProduct"附近的语法不正确。
我做错了什么?当我使用EXEC
句子进入另一个 sp 时,如何更改参数值@sp_Name?问候
更新:
正如亚历克斯评论的那样,我将sp更改为:
ALTER PROCEDURE [dbo].[SQLErrorCodes]
-- Add the parameters for the stored procedure here
@sp_Name varchar(max),
@ErrorNumber varchar(max) = ERROR_NUMBER,
@ErrorSeverity varchar(max) = ERROR_SEVERITY,
@ErrorState varchar (max) = ERROR_STATE,
@ErrorProcedure varchar (max) = ERROR_PROCEDURE ,
@ErrorLine varchar(max) = ERROR_LINE,
@ErrorMessage varchar(max) = ERROR_MESSAGE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO Exception.SqlTransaction VALUES (@sp_Name, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine,
@ErrorMessage)
现在我应该如何使用 EXEC 调用原始 sp?
应该不带括号
EXEC SQLErrorCodes 'usp_AddProduct'