当前位置：首页 > news >正文

sqlserver怎样动态执行存储过程，并且返回报错

news 2025/6/25 18:52:03

存储过程1

USE [OM]
GO
/****** Object:  StoredProcedure [dbo].[procTranDemo2]    Script Date: 2025/6/24 19:20:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE PROCEDURE [dbo].[procTranDemo2]@wo VARCHAR(255),@cartonId VARCHAR(255) OUTPUT
AS
DECLARE @sql VARCHAR(100);
DECLARE @errorMsg VARCHAR(4000);
DECLARE @SavePoint VARCHAR(32) = 'SP_' + CAST(@@SPID AS VARCHAR);
BEGIN
SET NOCOUNT ON; -- 禁用受影响行数的消息返回
SAVE TRANSACTION @SavePoint; 
BEGIN TRYPRINT @wo;--set @sql = 'delete from mes_zhiju_box where box_id = ''test'''--print @sql--exec(@sql)THROW 51000, '动态sql报错', 1;--select 'key1' as 'label_key', 'value1' as 'label_value'SET @cartonId = 'test0001';END TRY
BEGIN CATCHPRINT 'demo2';PRINT @@TRANCOUNT;SET @errorMsg = ERROR_MESSAGE();ROLLBACK TRANSACTION @SavePoint;-- 重新抛出原始错误（保留堆栈）THROW;
END CATCH
END

存储过程2

USE [OM]
GO
/****** Object:  StoredProcedure [dbo].[procTranDemo]    Script Date: 2025/6/24 19:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[procTranDemo]@wo varchar(255),@cartonId VARCHAR(255) OUTPUT
AS
DECLARE @sql NVARCHAR(MAX);
DECLARE @errorMsg VARCHAR(4000);
DECLARE @errorCode INT;
BEGIN
SET NOCOUNT ON; -- 禁用受影响行数的消息返回
BEGIN TRY;BEGIN TRAN;DECLARE @paramDefinition NVARCHAR(500);DECLARE @procName VARCHAR(50) = 'procTranDemo2';SET @sql = N'DECLARE @innerError INT, @innerMessage VARCHAR(4000);BEGIN TRY-- 执行目标存储过程EXEC '+ @procName +'@wo = '''+ @wo +''',@cartonId = @cartonId OUTPUTEND TRYBEGIN CATCH-- 捕获存储过程内部异常SET @innerError = ERROR_NUMBER();SET @innerMessage = ERROR_MESSAGE();END CATCH;-- 传递错误信息到外层SELECT @errorCode = @innerError, @errorMsg = @innerMessage;';SET @paramDefinition = N'@cartonId VARCHAR(100) OUTPUT,@errorCode INT OUTPUT,@errorMsg VARCHAR(4000) OUTPUT';EXEC sp_executesql @sql,                       -- 动态SQL字符串@paramDefinition,                  -- 参数定义@cartonId = @cartonId OUTPUT, -- 绑定：动态参数 -> 外部变量@errorCode = @errorCode OUTPUT,@errorMsg = @errorMsg OUTPUT;IF(@errorCode IS NOT NULL OR @errorMsg IS NOT NULL)BEGIN-- 自定义错误处理（如记录日志）PRINT '存储过程内部错误: ' + COALESCE(@errorMsg, '未知错误');-- 重新抛出错误THROW 51000, @errorMsg, 1;END;IF(@@TRANCOUNT > 0)BEGINCOMMIT TRAN;END 
END TRY
BEGIN CATCHPRINT 'demo1';PRINT @@TRANCOUNT;IF(@@TRANCOUNT > 0)BEGINSET @errorMsg = ERROR_MESSAGE()ROLLBACK TRANSACTIONEND;-- 重新抛出原始错误（保留堆栈）THROW;
END CATCH
END