March 22, 2012 at 4:49 am
Need to write a simple Insert stored procedure (Single insert)with proper error handling.. The sp should make the insert and return somthing to client application indicating the insert was successfull or not..
How to do it.. please demonstate.. weather to use raiseerror or return or output parameter or simple Select @@error
Need code for a single insert statement onlty..
March 22, 2012 at 4:54 am
you will want to take a look at the keywords, try, catch, xact_state, error_number, error_message and transaction which can be found in BOL and Google.
do the insert inside the try block which is wrapped in a transaction, then do the error handling in the catch block which rollback the transaction or commits it based on the transaction state
March 22, 2012 at 5:58 am
But there is only a single insert statement.. Do i need to put tat also in Withing Try catch transactuions.. I thought it should only by for multiple inserts... what about other options.. cant they work??
March 22, 2012 at 6:02 am
please provide sample code for a simple single insert statements that just returns status to the caller saying the insert ws successfull or not.. Sounds so simple but there are so many options which are confusing me.. neways i needs full code skeleton plz
March 22, 2012 at 6:03 am
if you provide what you have so far then we will tell you where you are going wrong
March 22, 2012 at 6:21 am
here's a pretty full fleshed model:
CREATE PROCEDURE EXAMPLEINSERT(
@Value VARCHAR(30))
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO SOMETABLE
(SomeField,ColumnList)
VALUES (@Value,'OtherValues') --i hope it's obvious this is a placeholder!
COMMIT TRANSACTION
RETURN 0 --no errors
END TRY
BEGIN CATCH
DECLARE
@ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d',
@ErrorSeverity,
@ErrorState,
@ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
RETURN @ErrorState --1, but anything nonzero means an error.
END CATCH
END --PROC
Lowell
March 22, 2012 at 6:21 am
itskanchanhere (3/22/2012)
Need to write a simple Insert stored procedure (Single insert)with proper error handling.. The sp should make the insert and return somthing to client application indicating the insert was successfull or not..How to do it.. please demonstate.. weather to use raiseerror or return or output parameter or simple Select @@error
Need code for a single insert statement onlty..
If the error happen in a single insert statement, it will be passed back to the caller as an error until it handled inside of procedure.
If, instead of error you want caller to get some kind of status, you should handle the error inside stored proc. The best way for that is to use TRY .. CATCH technique.
You don't need transaction control if your stored proc has only one insert statement. Also, most of system architectures would suggest to avoid controlling transaction in stored procs, but in DAL (except of cases where stored procedures are intended for independent use)
March 22, 2012 at 6:23 am
Lowell (3/22/2012)
here's a pretty full fleshed model:
CREATE PROCEDURE EXAMPLEINSERT(
@Value VARCHAR(30))
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO SOMETABLE
(SomeField,ColumnList)
VALUES (@Value,'OtherValues') --i hope it's obvious this is a placeholder!
COMMIT TRANSACTION
RETURN 0 --no errors
END TRY
BEGIN CATCH
DECLARE
@ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d',
@ErrorSeverity,
@ErrorState,
@ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
RETURN @ErrorState --1, but anything nonzero means an error.
END CATCH
END --PROC
May I ask why you wrapping a single insert with transaction? What does it give you, other than more code-lines?
March 22, 2012 at 6:28 am
absolutely no value or logical reason Eugene, you are right, other than it was what I thought the OP was asking for...a model that included try catch and features an insert.
Lowell
March 22, 2012 at 6:30 am
Below are the options I have.. We have try catch tracactions also but i dont want to use itsince that should be for multiple updates.
So I need to know which is the best way to do this out of the below options mentioned and what is the difference..
OPTION #1 (Using Raise error)
Use DB_NAme
go
CREATE PROCEDURE dbo.Insert_Sp
(
@param
)
AS
DECLARE @@ERRORCODE int
SET NOCOUNT ON
Insert Statement..............
SELECT @@ERRORCODE=@@Error
IF @@ERRORCODE <> 0
BEGIN
RAISERROR ('There was an error running the procedure Insert_Sp',18,127) WITH SETERROR
RETURN(1)
END
SET NOCOUNT OFF
RETURN
------------------------------------------
OPTION #2 (Using output parameters)
Use DB_NAme
Go
CREATE PROCEDURE dbo.Insert_Sp
( @param1
, @pStatus INT =0 OUTPUT
, @pErrorCode int=0 output
) AS
SET NOCOUNT ON
Insert statement .....
SELECT @pErrorCode=@@Error,@pStatus=@@ROWCOUNT
SET NOCOUNT OFF
RETURN
--------------------------------------------
Option #3 (Using return )
Use DB_NAme
Go
CREATE PROCEDURE dbo.Insert_Sp
( @param1
) AS
SET NOCOUNT ON
Insert statement .....
return @@Error
SET NOCOUNT OFF
RETURN
--------------------------------------------
Option #4 (Using select)
Use DB_NAme
Go
CREATE PROCEDURE dbo.Insert_Sp
( @param1
) AS
SET NOCOUNT ON
Insert statement .....
SELEct @@Error
SET NOCOUNT OFF
RETURN
March 22, 2012 at 6:41 am
OPTION #5 (Try ... Catch)
Use Db_name
Go
CREATE PROCEDURE Insert_Sp
( @param1
) AS
SET NOCOUNT ON
Begin Try
Insert statement .....
End Try
Begin catch
RAISERROR ('There was an error running the procedure Insert_Sp',18,127) WITH SETERROR
End Catch
SET NOCOUNT OFF
return
March 22, 2012 at 6:43 am
I wouldn't use any one of your's posted.
It is really depends on what you want your caller to get- Error or Status?
1. If error, then simpler analogue of your option one would be just:
CREATE PROCEDURE dbo.Insert_Sp ( @param )
AS
SET NOCOUNT ON
Insert Statement..............
RETURN
In case of error, caller will get as it is.
2. If you want to enhance the error details somehow (but leave it to be an error), you can "re-raise" the error.
3. If you don't want the error returned back to client at all, you can handle it and suppress it returning some kind of status in recordset (using select) or in procedure RETUNR value.
For option 2 and 3 I would do:
CREATE PROCEDURE dbo.Insert_Sp ( @param )
AS
SET NOCOUNT ON
BEGIN TRY
Insert Statement..............
END TRY
BEGIN CATCH
-- For option 2, get error details and reraise the error
-- For option 3 you can do just:
RETURN 1 -- fail
END CATCH
RETURN 0 --success
March 22, 2012 at 6:58 am
What i understand is.
1. If i want to return error as an error i can either use RAISEERROR with my custom error message or just do nothing and shown by your 1st example as that will anyways return the error message back to client
2. If a dont want to to return error as an error I can either do Select, return or use output param withing Catch block..
But Y is using catch block important.. If i just go with my option #2 or #3 or 4... what can go worng??
March 22, 2012 at 7:22 am
...
If i just go with my option #2 or #3 or 4... what can go worng??
...
Nothing. But in all of your cases the error will still be returned as error, and, if your caller code has an error handling, it will not likely to look into what you return as parameters, return value or recordset. Error handler will catch original SQL error.
Also, you don't need to SET NOCOUNT OFF at the end of your stored proc...
March 22, 2012 at 8:56 am
Eugene Elutin (3/22/2012)
Lowell (3/22/2012)
here's a pretty full fleshed model:
CREATE PROCEDURE EXAMPLEINSERT(
@Value VARCHAR(30))
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO SOMETABLE
(SomeField,ColumnList)
VALUES (@Value,'OtherValues') --i hope it's obvious this is a placeholder!
COMMIT TRANSACTION
RETURN 0 --no errors
END TRY
BEGIN CATCH
DECLARE
@ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d',
@ErrorSeverity,
@ErrorState,
@ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
RETURN @ErrorState --1, but anything nonzero means an error.
END CATCH
END --PROC
May I ask why you wrapping a single insert with transaction? What does it give you, other than more code-lines?
I'd say it gives you a standard way of writing the code with explicit control over the commit/rollback.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply