SQL defaults - best practice?

What is the best thing to do in the CREATE and UPDATE stored procedures for a table with default constraints?

When I create a new column for a table, I try to set a propper default value (Default constraint).

Example:

开发者_如何学Python

CREATE TABLE Orders
(
   O_ID INT NOT NULL
  ,State INT DEFAULT 0 --  0 => Not Verified, 1 => Verified, 2 => Processing ....
  ,P_ID INT
  ,OrderDate DATE DEFAULT GETDATE()
 )

What is the best thing to do in the CREATE and UPDATE stored procedures for this table? Use the same defaults as in the constraint?

CREATE PROCEDURE UpdateOrder
(
   @O_ID INT
  ,@State INT = 0
  ,@P_ID INT
  ,@OrderDate DATE
)
AS

UPDATE
   Orders
SET
   State = @State
  ,P_ID = @PID
  ,OrderDate = @OrderDate
WHERE
  O_ID = @O_ID

---

That would be kind of repetitive, as it's already defaulted in the table.

On the other hand, it allows your parameters to be optional. I would say your choices are to default them to the same as the table (as you suggest), or to default them to null and the table will fill in the default values. The second way is less repetitive and error-prone.

---

If you want OrderDate to be updated during UPDATE, you need to include it in the UPDATE statement and use getdate() in place of @OrderDate

UPDATE     
Orders 
SET     
State = @State   ,
P_ID = @PID   ,
OrderDate = getdate()
WHERE   O_ID = @O_ID 

---

To comply with DRY, one workaround would be to store your defaults in a table maybe:

CREATE TABLE dbo.MyDefaults
(
    OrderState INT
);
INSERT dbo.MyDefaults(OrderState) SELECT 0;

You can't really do this with GETDATE(), so let's just leave that as is - not something you're likely to change, anyway. So now we can pull our default value from the table, instead of hard-coding it. Let's create a scalar function, because we can't use a subquery in a default constraint:

CREATE FUNCTION dbo.DefaultOrderState()
RETURNS INT
AS
BEGIN
    RETURN (SELECT TOP (1) OrderState FROM dbo.MyDefaults);
END
GO

(If you have a lot of these, you might consider an EAV approach instead of dedicated columns.)

So now we can have our Orders table, and note that the constant "0" is never mentioned:

CREATE TABLE dbo.Orders
(
    O_ID INT NOT NULL,
    [State] INT NOT NULL DEFAULT (dbo.DefaultOrderState()),
    P_ID INT,
    OrderDate DATE NOT NULL DEFAULT (SYSDATETIME())
);
GO

And our update procedure can also grab the defaults (except you haven't defined whether you really want to reset the state to 0 if it is not currently 0 and no value is supplied to the procedure). Again the "0" constant is not mentioned.

CREATE PROCEDURE dbo.UpdateOrder
    @O_ID INT,
    @State INT = NULL,
    @P_ID INT,
    @OrderDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE dbo.Orders
        SET [State] = COALESCE(@State, dbo.DefaltOrderState()),
        P_ID = @P_ID,
        OrderDate = COALESCE(@OrderDate, OrderDate, SYSDATETIME())
    WHERE
        O_ID = @O_ID;
END
GO

---

For the update, you could send in null for "No Change" (or another sentinel if the column is nullable). A similar approach would work for inserts.

CREATE PROCEDURE UpdateOrder
(
   @O_ID INT
  ,@State INT
  ,@P_ID INT = -1
  ,@OrderDate DATE
)
AS

UPDATE
    Orders
SET
   State = IsNull(@State,State)
  ,P_ID = case when @P_ID < 0 then P_ID else @P_ID end  -- assuming this int is not nullable and something like -1 is the default value
  ,OrderDate = COALESCE(@OrderDate,OrderDate)
WHERE
  O_ID = @O_ID