Multiple conditional Where clause

问题:

I currently have a query that will pull a bunch of information from my database based on whatever where condition that I want to use.

declare @CaseNum char(7),
        @ImportId char,
        @FormatId char,
        @SessionId char(5)


set @CaseNum = ''  --I can place the value that I want to search by in here
set @ImportId = ''
set @FormatId = ''
set @SessionId = ''

--------------------
   query in here
--------------------

where 
     gr.[CaseNum] = @CaseNum  --currently I have to comment the ones I'm not using out
     --im.[ImportId] = @ImportId
     --fr.[FormatId] = @FormatId
     --se.[SessionId] = @SessionId

I want to be able to take the comment portion out and simply display all rows if the parameter = ''

For example if I use set @CaseNum = '1234567' then it will search by that parameter and if I use @FormatId = '12' it will search by that one.

I have tried using the following and a few other attempts but I am getting nowhere fast.

where 
     gr.[CaseNum] = '%' + @CaseNum + '%'
     and im.[ImportId] = '%' + @ImportId + '%'
     and fr.[FormatId] = '%' + @FormatId + '%'
     and se.[SessionId] = '%' + @SessionId + '%'

回答1:

You might want to look into building your query.

DECLARE @Number varchar(10)
DECLARE @Where varchar(max)
DECLARE @Query varchar(max)

SET @Query = 'SELECT * FROM TestTable'
SET @Where = ''

SET @Number = '3'

IF ISNULL(@Number, '') != ''
BEGIN
    SET @Where = @Where + 'and testNumber = ' + @Number
END

IF LEN(@Where) > 0
BEGIN
    SET @Where = SUBSTRING(@Where, 4, LEN(@Where))
END

if ISNULL(@Where, '') != ''
BEGIN
    SET @Query = @Query + ' WHERE ' + @Where
END

EXEC(@Query)

Check out this gentleman's article for reference: https://social.msdn.microsoft.com/forums/sqlserver/en-US/1ec6ddd9-754b-4d78-8d3a-2b4da90e85dc/dynamically-building-where-clauses

回答2:

With help from the link that @Norman posted I figured it out. I wanted to post my solution for others to see.

declare @CaseNum varchar(MAX),
        @ImportId varchar(MAX)


set @CaseNum = ''
set @ImportId = ''
----------------------------------------------------------------------------

If(@CaseNum = '')    --Sets the parameter to NULL for COALESCE to work
Begin
    Select @CaseNum = NULL
End

If(@ImportId = '')   --Sets the parameter to NULL for COALESCE to work
Begin
    Select @ImportId = NULL
End

--------------------
   query in here
--------------------

where
    gr.[CaseNum] = COALESCE(@CaseNum, gr.[CaseNum])
    and im.ImportId = COALESCE(@ImportId, im.ImportId)

This solution allows the query to use just a single parameter or all at the same time.