如何使用case语句来构建动态where子句 [英] How to use case statement to build dynamic where clause

问题描述

我有一个名为master_unittype的表，其中有uinttypeid和typename。

喜欢

unittypeid ------------- typename

1 ------------------------- kg

2 -------- ----------------- pc



现在我必须使用条件从此表中选择unittypeid，typename。 br />


另一个查询返回'kg'，'gm'，'pc'等我正在存储一个名为

@的变量usageunitName。



现在如果@ usageunitName ='kg'，我必须从master_unittype中选择unittypeid，typename，其中typename ='kg'

和

如果@ usageunitName ='pc'，我必须从master_unittype中选择unittypeid，typename，其中typename ='kg'或typename ='pc'



我尝试过：



声明@usageunitName varchar（50）;



从master_product内部联接master_unit_type中选择@ usageunitName = typename uct.productUnitTypeId = master_unit_type.unittypeid其中productid = 1;





选择unittypeid，来自master_unit_type的typename（（@ usageunitName =的情况） 'kg'然后（typename ='kg'）

当@ usageunitName ='pc'然后（typename ='kg'或typename ='pc'）

else typename =''

end））;

i have a table called master_unittype where uinttypeid and typename is there.
like
unittypeid-------------typename
1-------------------------kg
2-------------------------pc

now I have to select unittypeid,typename from this table using a condition.

some another query is returning 'kg','gm','pc' etc and i'm storing it a variable named
@usageunitName.

Now if @usageunitName='kg', i have to select unittypeid,typename from the master_unittype where typename='kg'
and
if @usageunitName='pc', i have to select unittypeid,typename from the master_unittype where typename='kg' or typename='pc'

What I have tried:

declare @usageunitName varchar(50);

select @usageunitName=typename from master_product inner join master_unit_type on master_product.productUnitTypeId=master_unit_type.unittypeid where productid=1;


select unittypeid,typename from master_unit_type where ((case when @usageunitName='kg' then (typename='kg')
when @usageunitName='pc' then (typename='kg' or typename='pc')
else typename=''
end));

推荐答案

如果你能组合最好的sql。例如，无论您在哪里SELECT @usageunitName = ...将该sql添加到where子句中的此sql其他sql。



如果你不能做一个动态的sql语句，这不是首选。



将您的sql更改为字符串

If you can combine the sql that would be best. For example, wherever you are SELECT @usageunitName = ... add that sql to this sql other sql in the where clause.

If you can't you have to do a dynamic sql statement, which is not preferred.

Change your sql to be a string

DECLARE @sql NVARCHAR (MAX)
SELECT @sql = 'SELECT ... FROM ... WHERE field1 = ' + @variable

EXEC (@sql)

WHERE



CASE WHEN（@usageunitName IS NULL或者@useuseunitName =''）

那么'1'

ELSE - 您的字段名称

END = CASE WHEN（@usageunitName IS NULL或@useununName =''）

那么'1'

ELSE @usageunitName

结束





----根据您的要求添加另一个条件。





Ashish

WHERE

CASE WHEN (@usageunitName IS NULL OR @usageunitName='')
THEN '1'
ELSE -- Your field Name
END = CASE WHEN (@usageunitName IS NULL OR @usageunitName='')
THEN '1'
ELSE @usageunitName
END


---- add another condition as per your requirment.


Ashish

这篇关于如何使用case语句来构建动态where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！