使用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

推荐答案

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

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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！