使用IN运算符的COALESCE进行SQL查询

本文介绍了使用IN运算符的COALESCE进行SQL查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

朋友们. 我写了一个查询，如下所示，它返回了所有客户记录.

Hi friends. I write one query as below and it returns all customer records.

DECLARE @CODE AS VARCHAR(20) SET @CODE = NULL SELECT * FROM CUSTOMER WHERE CUSTOMER.CUS_NO = COALESCE(@CODE,CUSTOMER.CUS_NO)

如果我设置@code = ''1004''，则它仅返回一个带有CUS_NO = 1004的客户.

If I set @code = ''1004'' then it returns only one customer with CUS_NO = 1004.

DECLARE @CODE AS VARCHAR(20) SET @CODE = '1004' SELECT * FROM CUSTOMER WHERE CUSTOMER.CUS_NO = COALESCE(@CODE,CUSTOMER.CUS_NO)

现在我的要求在这里. 我希望客户使用IN运算符列出.我想在@CODE变量中用单引号设置多个客户编号，并使用IN运算符，我希望获得所有客户. 像

Now My requirement is here. I want customers list with using IN operator. I want to set multiple customer number with single quote in @CODE variable and using IN operator, I want to get all customers. Like as

DECLARE @CODE AS VARCHAR(20) SET @CODE = '1004,1003' SELECT * FROM CUSTOMER WHERE CUSTOMER.CUS_NO IN (COALESCE(@CODE,CUSTOMER.CUS_NO))

我知道上面的语法对于IN运算符是错误的，但是我想要客户1004和1003的结果，如果我设置了@CODE = null，那么它应该返回所有记录.如果还有其他方法可以完成我的任务.我不想像字符串中的set查询一样执行内部查询，然后通过sp_executesql语句执行它. 谢谢 Imrankhan

I know the above syntax is wrong for IN operator but I want result of customer 1004 and 1003 and If I set @CODE = null then it should return all records. If there is any other way to accomplish my task. I do not want to execute Inner Query like set query in string and then execute it by sp_executesql statement. Thanks Imrankhan

推荐答案

星期五.尝试下面的代码 Hi Fri.. Try the below code CREATE TABLE #tempTable (CUS_NO int NOT NULL,) DECLARE @CODE AS VARCHAR(20), @tbl varchar(30), @delimiter char(1) = ',' , @sql varchar(8000), @select varchar(8000) SET @tbl='#tempTable' SET @CODE = '1,2,3,4,1003' SET @select = 'SELECT ' + REPLACE(@CODE, @delimiter, ' SELECT ') SET @select = REPLACE(@select, '''', '''''') SET @sql = 'INSERT ' + @tbl + ' EXEC(''' + @select + ''')' EXEC (@sql) SELECT * FROM CUSTOMER WHERE CUSTOMER.CUS_NO IN ( (select distinct CUS_NO from #tempTable UNION select CUSTOMER.CUS_NO) ) DROP TABLE #tempTable

感谢您的反馈.但是CUS_NO数据类型为nvarchar，并且您的查询给我错误消息将nvarchar值"NYAUDI"转换为数据类型int时转换失败"，因为其中一个值为"NYAUDI"，未转换为int. Thanks for you feedback. But CUS_NO data type is nvarchar and your query gives me error "Conversion failed when converting the nvarchar value ''NYAUDI'' to data type int" because one of value is "NYAUDI" that is not converted into int.

试试这个. TRY THIS. DECLARE @CODE AS VARCHAR(20) SET @CODE = '1004,1003' SELECT * FROM CUSTOMER WHERE CUSTOMER.CUS_NO IN (CASE WHEN ISNULL(@CODE,0)=0 THEN CUSTOMER.CUS_NO ELSE @CODE END)