我有一个存储过程,该过程返回一个结果集(一条select语句)和一个输出参数say @IsError
。仅当@IsError
设置为1时才返回ResultSet 。
现在,我正在从另一个过程中调用此过程,并且只想在@IsError
为1时才插入该过程返回的数据。
INSERT INTO #Order_Error(col1, col2, col3)
EXEC myStoredProc @param1, @param2, @IsError output
我想检查一下是否只有在#Order_Error
参数@IsError
为1的情况下才应插入数据。
供myStoredProc
定义的参考。
CREATE PROC myStoreProc
( @param1 int,
@param2 varchar(50),
@IsError bit output
)
AS
BEGIN
BEGIN TRY
-- My T-SQL operations like insert or update to the table. Nothing returns from this block i.e. No SELECT statement.
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage
SET @IsError = 1
END CATCH
END
我不确定该怎么做。任何帮助将不胜感激。谢谢
在存储过程中更新CATCH语句以包括INSERT语句:
CREATE PROC myStoreProc
( @param1 int,
@param2 varchar(50),
@IsError bit output
)
AS
BEGIN
BEGIN TRY
-- My T-SQL operations like insert or update to the table. Nothing returns from this block i.e. No SELECT statement.
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage
SET @IsError = 1
IF (@IsError = 1)
BEGIN
<INSERT statement for #Order_Error>
END
END CATCH
END
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句