Is it possible to make parameter to use in where clause in stored procedure?

Question

I am using SQL Server 2012.

I have this table named myDataTable:

| Id | ExternalId | Subject | CMT           |    
| 1  |   3379     | damage5 | some comment7 |
| 2  |   3380     | damage3 | some comment3 | 
| 3  |   3382     | damage4 | some comment5 |
| 4  |   3381     | damage1 | some comment4 |

The ExternalId column is of type Int.

The CMT column is of type nvarchar(max).

I pass to stored procedure parameter @filterParam of string type.

In stored procedure I need to check if @filterParam contains at least one letter I use @filterParam to filter selection with Subject column, like that :

ALTER PROCEDURE [dbo].[SPGeneralReport]
            @filterParam nvarchar(max),
            @CMTParam nvarchar(max)
AS
BEGIN

SET NOCOUNT ON;
SELECT      *                
FROM        myDataTable
WHERE       Subject = @filterParam AND CMT = @CMTParam
END

In stored procedure I need to check if @filterParam contains only digits I need to cast it to integer and use it in WHERE clause to filter selection with ExternalId column :

ALTER PROCEDURE [dbo].[SPGeneralReport]
            @filterParam nvarchar(max)
AS
BEGIN

SET NOCOUNT ON;
SELECT      *                
FROM        myDataTable
WHERE       ExternalId = @filterParam AND CMT = @CMTParam
END

If @filterParam is NULL I don't want to use it at all in my filter:

ALTER PROCEDURE [dbo].[SPGeneralReport]
            @filterParam nvarchar(max)
AS
BEGIN

SET NOCOUNT ON;
SELECT      *                
FROM        myDataTable
WHERE       CMT = @CMTParam 
END

I don't want to create multiple stored procedures, I want to create single procedure with optional parameter for filter.

Any idea how can I implement it?

Possible duplicate of [How can I use optional parameters in a T-SQL stored procedure?](http://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure) — TT., Oct 26 '16 at 11:01

score 1 · Answer 1 · answered Oct 26 '16 at 10:59

1

use OR condition

WHERE (ExternalId = @filterParam OR @filterParam IS NULL)
  AND CMT = @CMTParam

When @filterParam is NULL the first condition will not be applied

answered Oct 26 '16 at 10:59

Pரதீப்

91,748
19
131
172

1

This will work, but generally these "catch all" queries don't perform as well as they could. See: https://www.simple-talk.com/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/ – HoneyBadger Oct 26 '16 at 11:03
@HoneyBadger - Parameter sniffing is kind of hit or miss – Pரதீப் Oct 26 '16 at 11:14
Cf the duplicate-link in comments on the question... Adding `OPTION(RECOMPILE)` in cases like these is not a bad idea. – TT. Oct 26 '16 at 13:24

TYY · Answer 2 · 2016-10-26T13:12:03.410

Since you have 3 cases in this situation I would do something like this. Filter out the common clause first, then use condition statements on the other filter ( note I had to enable Ole Automation Procedures).

Create Table #Temp
(
    Id INT,
    ExternalId Int,
    [Subject] NVarChar(128),
    Comment NVarChar(128)
)

Insert Into #Temp
Values

( 1  ,   3379     ,'damage5' ,'some comment7' ),
( 2  ,   3380     ,'damage3' ,'some comment4' ),
( 3  ,   3382     ,'damage4' ,'some comment5' ),
( 4  ,   3381     ,'damage1' ,'some comment4' )

Declare @filterParam nvarchar(max) = '3380', 
        @CMTParam nvarchar(max) = 'some comment4'

Select * Into #OtherTemp
From #Temp
Where Comment = @CMTParam

IF(@filterParam IS NULL)
Begin
    Select * 
    From #OtherTemp
End
Else If (dbo.fn_regex('^\d*$',@filterParam) <> 0)
Begin
    Select * 
    From #OtherTemp
    where ExternalId = Cast(@filterParam AS Int)
End
Else
Begin
    Select * 
    From #OtherTemp
    where Subject= @filterParam
End

Drop Table #Temp
Drop Table #OtherTemp

Another alternative, would be to create 2 variables and perform your parsing there and use the resultant in your where clause

Declare @filterSubject NVarChar(128), @filterId Int

If (dbo.fn_regex('^\d*$',@filterParam) <> 0)
Begin
    SET @filterId = Cast(@filterParam AS Int)
End
Else If (@filterParam IS NOT NULL)
Begin
    SET @filterSubject =  @filterParam
End 


Select * 
From #Temp
Where (@filterId IS NULL OR ExternalId = @filterId) 
        AND (@filterSubject IS NULL OR Subject =@filterSubject)
        AND Comment = @CMTParam

Is it possible to make parameter to use in where clause in stored procedure?

2 Answers2