Saturday, November 29, 2014

Validate Order

Hi folks, looking for some help, my store proc saves customer order details to ms sql database and is fired by the user clicking on a command button.


I need the stored proc to validate if the OrderID already exists within the database before saving the order, not really sure how to do this. Code below,



USE [TyreSanner]
GO
/****** Object: StoredProcedure [dbo].[AddCustomerDetails] Script Date: 11/27/2014 22:32:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddCustomerDetails]

/***********************Declare variables ***********************/

/******tblCustomer******/
@Forename nvarchar(50),
@Surname nvarchar(50),
@HouseNo nvarchar(50),
@CustAddress nvarchar(50),
@Town nvarchar(50),
@Postcode nvarchar(50),
@ContactNo nvarchar(50),
@EmailAddress nvarchar(50),

/******tblLink_OrderProduct******/
@ProductQuantity int,
@TotalProductSaleCost decimal,
@ProductFK int,
@FittingDate date,
@FittingTime Time
As
DECLARE @CustomerFK int;
DECLARE @OrderFK int;
Begin TRANSACTION
SET NOCOUNT ON

INSERT INTO [TyreSanner].[dbo].[Customer](Forename, Surname, HouseNo, CustAddress, Town, Postcode, ContactNo, EmailAddress)
VALUES (@Forename,@Surname,@HouseNo,@CustAddress,@Town,@Postcode,@ContactNo,@EmailAddress)


Set @CustomerFK = SCOPE_IDENTITY()

INSERT INTO [TyreSanner].[dbo].[Order] (CustomerFK)
VALUES (@CustomerFK)


SET @OrderFK = SCOPE_IDENTITY()

INSERT INTO [TyreSanner].[dbo].[Link_OrderProduct](OrderFK, ProductFK, ProductQuantity, TotalProductSaleCost, FittingDate, FittingTime)

VALUES
(@OrderFK, @ProductFK, @ProductQuantity, @TotalProductSaleCost, @FittingDate, @FittingTime)

SELECT SCOPE_IDENTITY() AS Link_Id;



COMMIT TRANSACTION


No comments:

Post a Comment