Re: Stored procedure error handling

Hi

Look at http://www.sommarskog.se/error-handling-II.html

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@xxxxxxxxxxxx

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"dtwilliams@xxxxxxxxxxx" <dan_williams@xxxxxxxxxxxxxxxxxxxx> wrote in
message news:1124986139.362538.125920@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> OK, i'm trying to do some error checking on stored procedures and am
> following the advise in Erland Sommarskog's 'Implementing Error
> Handling with Stored Procedures' document.
>
> Can anybody help with my stored procedures and why it keeps erroring at
> the '-- Create new Address Detail stage'? The errorCode value that is
> being return in my web app is 0, so i'm not even sure why it's even
> raising the error!!
>
> Rather than executing the INSERT INTO AddressDetail in my
> CreateSupplier procedure and checking for errors, i'd like to be able
> execute a CreateAddressDetail SP, so that i can reuse it throughout my
> web app.
>
> New suppliers must have a contact address associated with it, so if
> there's an error creating the suppliers address, i need my
> CreateSupplier stored procedure to ROLLBACK and not create the new
> supplier. That's why i'm not doing two separate calls to the procedures
> from my app code.
>
> Any suggestions are most appreciated.
>
> Many thanks
>
> Dan Williams.
>
>
>
> CREATE PROCEDURE CreateSupplier
> @supplierName varchar(50),
> @userId bigint,
> @address varchar(50),
> @town varchar(50),
> @county varchar(50),
> @postCode varchar(15),
> @contactName varchar(50)
> AS
> BEGIN
>
> DECLARE @newSupplierId as bigint
> DECLARE @newAddressDetailId as bigint
> DECLARE @errorCode as bigint
>
> SET NOCOUNT ON
>
> BEGIN TRAN
>
> INSERT INTO Supplier
> (supplierName, accOpenedBy, accOpenedDate)
> VALUES (@supplierName, @userId, getDate())
>
> SET @newSupplierId = SCOPE_IDENTITY()
>
> -- Check for an error creating new supplier
> SELECT @errorCode = @@ERROR
> IF (@errorCode <> 0) BEGIN ROLLBACK TRAN RAISERROR ('Error creating
> supplier',16,1) RETURN @errorCode END
>
> -- Create new Address Detail
> EXEC @errorCode = CreateAddressDetail @address, @town, @county,
> @postCode, @contactName, @newAddressDetailId OUTPUT
>
> SELECT @errorCode = coalesce(nullif(@errorCode, 0), @@error)
>
> if @errorCode <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error creating
> address. ErrorCode = %d',16, @errorCode) RETURN @errorCode END
>
> COMMIT TRAN
> SET NOCOUNT OFF
> RETURN @newSupplierId
>
> END
> GO
>
>
>
> CREATE PROCEDURE CreateAddressDetail
> @address varchar(50),
> @town varchar(50),
> @county varchar(50),
> @postCode varchar(15),
> @contactName varchar(50),
> @newAddressDetailId bigint OUTPUT
>
> AS
> BEGIN
>
> -- Create new AddressDetail
>
> DECLARE @errorCode as bigint
>
> SET NOCOUNT ON
>
> BEGIN TRAN
>
> INSERT INTO AddressDetail
> (address, town, county, postCode, contactName)
> VALUES (@address, @town, @county, @postCode, @contactName)
>
> SET @newAddressDetailId = SCOPE_IDENTITY()
>
> -- Check for an error creating new address
> SELECT @errorCode = @@ERROR
> IF (@errorCode <> 0)
> BEGIN
> RAISERROR ('Error creating new address detail',16,1)
> ROLLBACK TRAN
> END
> ELSE
> COMMIT TRAN
> SET NOCOUNT OFF
> RETURN @newAddressDetailId
> END
> GO
>


.

Relevant Pages

  • Re: adding index NETS "Deadlock" ???
    ... > Add a RowID BigINT Identityto the beginning of the table. ... > Write stored procedures to do inserts, updates, and deletes. ... >> placed on StartTime. ... >> I add a nonclustered index on StartTime and get Deadlocks in Production. ...
    (microsoft.public.sqlserver.server)
  • Re: Updating sql05 with Stored Procedure
    ... Have you provided stored procedures for select/insert/update/delete? ... @Original_CategoryID int, ... SET NOCOUNT OFF; ... SR.lutCategories WHERE (CategoryID = SCOPE_IDENTITY()) ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Stored procedure executes twice
    ... The SET NOCOUNT issue is an unrelated issue to yours (judging by your ... duplicate executions. ... >> Do your stored procedures contain SET NOCOUNT ON? ... >> used ADO); try changing the lock types to see if that helps. ...
    (microsoft.public.sqlserver.programming)
  • RE: What stored procedures call this stored procedure?
    ... The first one select the information from the system table SYSCOMMENTS (see ... I see no such maintained relationships in the system tables between ... > a stored procedure and other stored procedures. ... >> set nocount on ...
    (microsoft.public.sqlserver.programming)
  • Re: SPs or triggers
    ... would be sending ad-hoc query statements from your application to SQL Server ... Stored procedures help you abstract your ... To import user data by registration whit SP or Trigger? ... > @ValuePrice BIGINT, ...
    (microsoft.public.sqlserver.programming)