多条件Where子句 [英] Multiple conditional Where clause
问题描述
我目前有一个查询,它将根据我要使用的where
条件从数据库中提取一堆信息.
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
如果参数=''
例如,如果我使用set @CaseNum = '1234567'
,它将按该参数进行搜索,如果我使用@FormatId = '12'
,它将按该参数进行搜索.
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 + '%'
推荐答案
您可能需要考虑构建查询.
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
这篇关于多条件Where子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!