SQL Server stored procedure to accept optional parameter

Question

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

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.

Why is it not working fine? Does it throw an error or return an unexpected result? — Nick.Mc
– Nick.Mc, Commented May 3, 2018 at 5:45
The expected out come is when there are multiple parameter, it should return combination results of all the parameter values. it was returning either of the parameter. — Jyothi
– Jyothi, Commented May 4, 2018 at 13:58

gomory-chvatal · Accepted Answer · 2018-05-03 04:19:40Z

1

you are missing parenthesis around the around the server section of the where clause, also you can use is null instead of 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

answered May 3, 2018 at 4:19

gomory-chvatal

3321 gold badge3 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server stored procedure to accept optional parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related