CREATE PROCEDURE MyProcName
@Parameter1 INT = 1,
@Parameter2 VARCHAR (100) = 'StringValue',
@Parameter3 VARCHAR (100) = NULL
AS
/* check for the NULL / default value (indicating nothing was passed */
if (@Parameter3 IS NULL)
BEGIN
/* whatever code you desire for a missing parameter*/
INSERT INTO ........
END
/* and use it in the query as so*/
SELECT *
FROM Table
WHERE Column = @Parameter
上面提到的默认设置只适用于简单的情况。在更复杂的情况下,如果参数为 NULL 或空且需要进行计算,则在 proc 的开头附近使用 IF 子句来提供值。
我经常在 WHERE 子句中使用可选的 parms,发现 SQL 不会短路逻辑,因此使用 CASE 语句确保不要尝试计算 NULL 或空日期或唯一标识符,如下所示:
CREATE Procedure ActivityReport
(
@FromDate varchar(50) = NULL,
@ToDate varchar(50) = NULL
)
AS
SET ARITHABORT ON
IF @ToDate IS NULL OR @ToDate = '' BEGIN
SET @ToDate = CONVERT(varchar, GETDATE(), 101)
END
SELECT ActivityDate, Details
FROM Activity
WHERE
1 = CASE
WHEN @FromDate IS NULL THEN 1
WHEN @FromDate = '' THEN 1
WHEN ActivityDate >= @FromDate AND ActivityDate < DATEADD(DD,1,@ToDate) THEN 1
ELSE 0
END