I have an X action in my application which, when it is executed, triggers a stored procedure in the database that generates a insert in a table. So far, so peaceful.
What I'm needing is a way that when this stored procedure is executed, I get back what has been executed and whether it has failed or not.
Ex: I have 5 actions, when I execute them, I need a return that tells me what worked and what was wrong to be shown on a grid for the User.
You can use try-catch inside sp:
BEGIN TRY
SELECT 1/0;
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;
END CATCH;
If you want to force an error depending on the logic of the sp, use the RAISERROR
If you are using an older version of SQL Server, you can open a transaction and commit or rollback according to a condition or error.
DECLARE @E INT
BEGIN TRAN
SELECT 1/0;
SET @E = @@ERROR
SELECT @E
IF @E > 0
ROLLBACK TRAN
ELSE
COMMIT TRAN