SQL Server存储过程中的可选参数? [英] optional parameters in SQL Server stored proc?

问题描述

我正在SQL Server 2008中编写一些存储的proc，并且想知道是否可以在此处使用可选输入参数的概念?

I'm writing some stored procs in SQL Server 2008, and wondered if the concept of optional input parameters is possible here?

我想我总是可以为不想使用的参数传递NULL，检查存储的proc中的值，然后从那里获取内容，但是我很想知道这里是否有这个概念.谢谢！

I suppose I could always pass in NULL for parameters I don't want to use, check the value in the stored proc, then take things from there, but I was interested if the concept is available here. Thanks!

推荐答案

您可以这样声明

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

这篇关于SQL Server存储过程中的可选参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！