>-- ============================================= >-- Create procedure with OUTPUT Parameters >-- ============================================= >-- creating the store procedure >IF EXISTS (SELECT name > FROM sysobjects > WHERE name = N'USP_Insert_ShoppingCartInfo' > AND type = 'P') > DROP PROCEDURE USP_Insert_ShoppingCartInfo >GO > >CREATE PROCEDURE USP_Insert_ShoppingCartInfo( > @tcUserID varchar(20), > @tnNumOfRecords int, > @tcDescription text, > @tcCreditCategory char(1), > @tcSelectionCriteria text, > @tcListOfCodes varchar(500), > @tnCardID int OUTPUT) >AS > >/* >CREATED BY: Nadya Nosonovsky >CREATED ON: 28/2/2003 >PURPOSE: This routine inserts records into Cart, CartContent and CartDetail tables > It passes UserID, Description, SelectionCriteria, NumberOfRecords, > and list of CredID separated by comma to insert into CartDetail table >*/ > >-- First do basic validation > > IF @tcUserID IS NULL > BEGIN >-- Mike Levy suggests > RAISERROR ('ERROR: The UserID value must contain a value.',16,1) > RETURN 1 > END > > -- Check all 3 tables existance > IF NOT EXISTS > (SELECT * FROM INFORMATION_SCHEMA.TABLES > WHERE TABLE_NAME = 'Cart') > BEGIN > RAISERROR ('ERROR: The Cart table does not exist.',16,1) > RETURN 2 > END > > IF NOT EXISTS > (SELECT * FROM INFORMATION_SCHEMA.TABLES > WHERE TABLE_NAME = 'CartContent') > BEGIN > RAISERROR ('ERROR: The CartContent table does not exist.',16,1) > RETURN 2 > END > > IF NOT EXISTS > (SELECT * FROM INFORMATION_SCHEMA.TABLES > WHERE TABLE_NAME = 'CartDetail') > BEGIN > RAISERROR ('ERROR: The CartCDetail table does not exist.',16,1) > RETURN 2 > END > >-- Now do the actual updates/inserts and check for possible errors code > > > BEGIN TRANSACTION >/* > If exists, grab CartID and update LastModified field > otherwise insert a record and grab generated CartID >*/ > >/* The code bellow belongs to Bob Archer > See Title: Re: SP with multiple tables and databases > (insert & delete) Thread #758108 Message #758212 > According to Mike Levy another variant (see bellow) > will be much better on your locks and concurrency > > UPDATE cart > SET LastModTs = CURRENT_TIMESTAMP, > @CartID = CartID > FROM cart > WHERE UserID = @tcUserID > > IF @@RowCount = 0 --Record doesn't exist > BEGIN > INSERT INTO cart (UserID) values (@tcUserID) > SELECT @CartID = SCOPE_IDENTITY() > END >*/ > >-- Code from Mike Levy >DECLARE @CartID int, @Err int, @CartContentID int > >-- PK in the Cart table >SET @CartID = 0 >-- PK in the CartContent table >SET @CartContentID = 0 >-- Error code >SET @Err = 0 > >IF EXISTS (SELECT CartID FROM cart WHERE UserID = @tcUserID) > BEGIN > UPDATE cart > SET @CartID = CartID, > LastModTs = CURRENT_TIMESTAMP > WHERE UserID = @tcUserID > SELECT @Err = @@ERROR > > IF @Err !=0 -- Error occurred > BEGIN > RAISERROR ('Can not update Cart table for the User %s' ,16,1, @tcUserID) > GOTO _Cleanup > END > END >ELSE >-- New User > BEGIN > INSERT INTO cart (UserID) values (@tcUserID) > SELECT @Err = @@ERROR, @CartID = SCOPE_IDENTITY() > IF @Err !=0 -- Error occurred > BEGIN > RAISERROR ('Can not Create Cart for the User %s' ,16,1, @tcUserID) > GOTO _Cleanup > END > END > > >-- Now continue with CartContent table > > INSERT INTO CartContent > (CartID, NumOfRecords, > CreditCategory, DescriptionText, SelectionCriteria) > values (@CartID, @tnNumOfRecords, @tcCreditCategory, > @tcDescription, @tcSelectionCriteria) > SELECT @Err = @@ERROR, @CartContentID = SCOPE_IDENTITY() > > IF @Err !=0 -- Error occurred > BEGIN > RAISERROR ('Can not add CartContent for the User %s' ,16,1, @tcUserID) > GOTO _Cleanup > END > >-- Will have to also parse list of codes and insert into CartDetail table > >_Cleanup: >IF @err != 0 > BEGIN > ROLLBACK TRANSACTION > RETURN @Err -- Return actual Error code - could be helpful > END >ELSE > BEGIN > COMMIT TRANSACTION > RETURN 0 >END >GO >-- Now test procedure >declare @tnCartID int, @RetStat int > >EXECUTE @RetStat = USP_Insert_ShoppingCartInfo > '241111111111' , > 5 , > 'Some descr' , > 'B' , > 'some crit' , > '1,2,3' , > @tnCartID OUTPUT > >print @tnCartID >print @RetStat >GO>