使用IN运算符的COALESCE进行SQL查询 [英] SQL query with COALESCE with IN operator

问题描述

朋友们.

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

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)

这篇关于使用IN运算符的COALESCE进行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！