Insert Data into a Table if a condition is met with SQL Server Stored Procedure

Question

I need to learn how to build a stored procedure in SQL Server 2014 that will insert data into a table - but only if a condition is met.

I will use a basic example:

I have 2 tables, customers and orders with a simple one-to-many relationship on the CustomerID.

Customers table contains:
CustomerID, CustomerName, TermStartDate, TermEndDate

Orders table contains:
contains OrderID, CustomerID, ProductID, OrderDate

I would like my stored procedure to insert a new record into the orders table with the parameters CustomerID, ProductID, OrderDate

ASK:
However, I would only like to insert the record if the OrderDate is between the customer's TermStartDate and TermEndDate

I'm obviously a SQL stored procedure newbie and I don't understand my options in terms of what could happen if the condition is not met, e.g. the record is not inserted and an error message is returned(?). I would also like the stored procedure to consider whether another user is also making changes to the underlying data when the stored procedure is executing (if that needs considering)

Answer 1

Going with the return value of the inserted identity value or -1 when not inserted, you can create a stored procedure with the following definition:

CREATE PROCEDURE dbo.Orders_Insert
    @CustomerId INT,
    @ProductId INT,
    @OrderDate DATETIME2
AS
BEGIN

    SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRAN;

    IF EXISTS ( SELECT  NULL
                FROM    Customers
                WHERE   CustomerId = @CustomerId AND
                        @OrderDate BETWEEN TermStartDate AND TermEndDate )
    BEGIN
        INSERT  INTO Orders
                (CustomerID,
                 ProductID,
                 OrderDate)
        SELECT  @CustomerId,
                @ProductId,
                @OrderDate;

        SELECT  SCOPE_IDENTITY();
    END;
    ELSE
    BEGIN
        SELECT  -1;
    END;

    COMMIT TRAN;
END;
GO

Note the explicit transaction isolation level definition and the condition and insert wrapped in a transaction will ensure concurrent calls will not interfere with each other.

Answer 2

I would only like to insert the record if the OrderDate is between the customer's TermStartDate and TermEndDate

   create proc usp_orders   
    (
    @CustomerID int
    @ProductID int,
    @ OrderDate datetime
    )
    as
    begin
    if exists(select 1 from dbo.customers where customerid=@customerid and  @orderdate >= termstartdate and @orderdate<= termenddate)
    Begin
    Insert into Dbo.orders
    select @customerid,@productid,@orderdate 
    End
    End

I don't understand my options in terms of what could happen if the condition is not met, e.g. the record is not inserted and an error message is returned(?)

if the condition is not met ,no record will be inserted

I would also like the stored procedure to consider whether another user is also making changes to the underlying data when the stored procedure is executing (if that needs considering)

When two users execute same procedure at same time,SQL will take care of concurrency issues for you.