CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Jyothi
Jyothi

Reputation: 109

SQL Server stored procedure to accept optional parameter

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.

Upvotes: 0

Views: 64

Answers (1)

gomory-chvatal
gomory-chvatal

Reputation: 332

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

Upvotes: 1

Related Questions