SQL Server存储过程接受可选参数

I have a table like below and I have to prepare a SQL Server stored procedure to accept the parameter to filter on a single column or multiple columns to retrieve the info from this table

我有一个像下面的表,我必须准备一个SQL Server存储过程来接受参数来筛选单个列或多个列以从此表中检索信息

SERVER  Application Environment
--------------------------------
SRV1    APP1    ENV1
SRV2    APP2    ENV1
SRV3    APP1    ENV2
SRV4    APP3    ENV1
SRV5    APP2    ENV2

The procedure should accept parameters are like below

该程序应该接受如下参数

EXEC GetSrvinfo @server = 'SRV1'
EXEC GetSrvinfo @application = 'APP1'
EXEC GetSrvinfo @environment = 'ENV1'
EXEC GetSrvinfo @environment = 'ENV1', @application = 'APP1'
EXEC GetSrvinfo @server = 'SRV1', @application = 'APP1'

So I have prepared this procedure with below stored procedure

所以我用下面的存储过程准备了这个程序

CREATE PROCEDURE [dbo].[GetSrvinfo]
    (@server VARCHAR(10) = NULL, 
     @application VARCHAR(10) = NULL,
     @environment VARCHAR(10) = NULL)
AS 
BEGIN
    SELECT *
    FROM [SRVINFO] WITH (NOLOCK)
    WHERE [SERVER] = @server OR COALESCE(@server,'') = ''
      AND ([Application] = @application OR COALESCE(@application, '') = '')
      AND ([Environment] = @environment OR COALESCE(@environment, '') = '')
END     

It is working fine for any single parameters but not working for the multiple parameters.

它适用于任何单个参数,但不适用于多个参数。

1 个解决方案

#1

you are missing parenthesis around the around the server section of the where clause, also you can use is null instead of coalesce(@, '') = ''

你在where子句的服务器部分周围缺少括号,也可以使用null而不是coalesce(@,'')=''

CREATE PROCEDURE [dbo].[GetSrvinfo]
(@server varchar(10) =  null, @application varchar(10) =  null, @ environment varchar(10) =  null)
AS 
BEGIN
SELECT *
        FROM 
        [SRVINFO] WITH (NOLOCK)
        WHERE ([SERVER] = @server OR @server is null)
        AND ([Application] = @application OR @application is null)
        AND ([Environment] = @environment OR @environment is null)
END