在SQL Server存储的过程中使用检查约束

Question

我想在存储过程上创建一个检查约束，该过程在1990年1月1日之前无法进行发票日期，并且不能在9999年12月31日之前进行。

现在，我有这样的SQL脚本设置：

CREATE PROCEDURE dbo.[getBalanceByDueDateRange]
    @MINDATE DATETIME = '1990-01-01 00:00:00',
    @MAXDATE DATETIME = '9999-12-31 00:00:00'
AS       
    SELECT  
        i.InvoiceID AS 'Invoice ID',
        v.VendorID AS 'Vendor ID',
        CONVERT(VARCHAR (12), i.InvoiceDueDate, 107) AS 'Invoice Due Date',
        ABS(i.InvoiceTotal - i.PaymentTotal) AS 'Balance'
    FROM 
        dbo.[Vendors] v
    INNER JOIN 
        dbo.[Invoices] i ON v.VendorID = i.VendorID
    WHERE   
        i.InvoiceDueDate BETWEEN ISNULL(@MINDATE, i.InvoiceDate) AND ISNULL(@MAXDATE, i.InvoiceDate)
    ORDER BY 
        i.InvoiceDueDATE ASC;

如果MINDATE或MAXDATE不在该范围内，我想做的就是返回错误。我认为如果/其他语句，我可能必须使用，但是还有另一种使用CHECK CONSTRAINT吗？存储的过程确实有效(因为它没有返回(，但我希望它更有效和专业。如果您有任何建议，我很想听听。

Answer 1

多亏了戈登·林夫(Gordon Linoff(的建议，我弄清楚了，谢谢戈登！

CREATE PROCEDURE dbo.[getBalanceByDueDateRange]
    @MINDATE DATETIME   = '1990-01-01 00:00:00' 
,       @MAXDATE DATETIME   = '9999-12-31 00:00:00'
AS
IF @MINDATE < '1900-01-01 00:00:00'
RAISERROR ('Please Select a Valid Date',0,1)
ELSE IF  @MAXDATE > '9999-12-31 00:00:00'
RAISERROR ('Please Select a Valid Date',0,1)
ELSE IF @MINDATE > @MAXDATE
RAISERROR ('The Minimum Date can`t be later than the Maximum Date',0,1)
ELSE
SELECT  i.InvoiceID AS 'Invoice ID'
,       v.VendorID AS 'Vendor ID' 
,       CONVERT(VARCHAR (12),i.InvoiceDueDate,107)AS 'Invoice Due Date'
,       ABS(i.InvoiceTotal - i.PaymentTotal) AS 'Balance'
FROM dbo.[Vendors] v
INNER JOIN dbo.[Invoices] i ON v.VendorID = i.VendorID
WHERE   i.InvoiceDueDate BETWEEN ISNULL(@MINDATE, i.InvoiceDate) AND
        ISNULL(@MAXDATE, i.InvoiceDate)
ORDER BY i.InvoiceDueDATE ASC;

Answer 2

您可以使用RisherRor并从下面的过程中返回。正如布兰登提到的约束不适合代码

的那样

CREATE PROCEDURE dbo.[getBalanceByDueDateRange]
        @MINDATE DATETIME   = '1990-01-01 00:00:00' 
,       @MAXDATE DATETIME   = '9999-12-31 00:00:00'
AS       
BEGIN
    IF @MINDATE > @MAXDATE 
    BEGIN
        RAISERROR('MinDate is greater than MaxDate', 20, -1)
        RETURN
    END
    SELECT  i.InvoiceID AS 'Invoice ID'
    ,       v.VendorID AS 'Vendor ID' 
    ,       CONVERT(VARCHAR (12),i.InvoiceDueDate,107)AS 'Invoice Due Date'
    ,       ABS(i.InvoiceTotal - i.PaymentTotal) AS 'Balance'
    FROM dbo.[Vendors] v
    INNER JOIN dbo.[Invoices] i ON v.VendorID = i.VendorID
    WHERE   i.InvoiceDueDate BETWEEN ISNULL(@MINDATE, i.InvoiceDate) AND
            ISNULL(@MAXDATE, i.InvoiceDate)
    ORDER BY i.InvoiceDueDATE ASC;
END