SQL-ID在哪里(ID列表) [英] SQL - where Id in (list of Ids)

问题描述

--SQL EXECUTION QUERY --
declare @Ids varchar(100) set @Ids='7,13,120'
select * from table where Id in (@Ids)
--SQL EXECUTION QUERY --

这导致我将varchar值"7,13,120"转换为数据类型int时转换失败."

我想在整数类型上实现"WHERE IN"查询，但是我的值将存储在可以是int或varchar任何类型的局部变量中.

This results me "Conversion failed when converting the varchar value ''7,13,120'' to data type int."

I want to implement "WHERE IN" query on integer type but my values will be stored in a local variable which can be of any type int or varchar.

推荐答案

已看一下StackOverflow上的这个问题，它与您想要执行的操作类似

http://stackoverflow.com/questions/1707326/define-变量在操作员中使用-t-sql [答案 [

Have a look at this question on StackOverflow, which is similar to what you want to do

http://stackoverflow.com/questions/1707326/define-variable-to-use-with-in-operator-t-sql[^]

Personally, I like the answer by LukeH[^], e.g.

DECLARE @MyList TABLE (Value INT)
INSERT INTO @MyList VALUES (1)
INSERT INTO @MyList VALUES (2)
INSERT INTO @MyList VALUES (3)
INSERT INTO @MyList VALUES (4)

SELECT *
FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

通读您可用的不同选项

Have a read through the different options available to you

尝试一下

try this

declare @Ids varchar(100)
set @Ids='100,200,300'
declare @query as nvarchar(max)
set @query='select * from table where Id in (' +@Ids +')'
exec sp_executesql @query

Pinak，

您的查询

Hi Pinak,

your query

select * from table where Id in ('7,3,120')

返回错误，因为它将整数列与varchar值进行比较. ``7,3,120''.
SQL Server引擎尝试将varchar值解析为整数.由于存在``，''，因此会出错.

以下查询将返回结果集...

returns error, because it is comparing integer column with varchar value ie. ''7,3,120''.
Sql server engine tries to parse the varchar value into integer. Because of '','', it gives error.

Following query will return the result set ...

select * from table where Id in ('7','3','120')

但是它仍然是静态的，要使其动态化，您需要拆分varchar值.
以下查询有助于获得结果集，而不会出现任何错误.

But still it is static.To make it dynamic, you need to split the varchar value.
Following query helps to get the result set with out getting any error.

DECLARE @ids VARCHAR(100) = '7,3,120';
DECLARE @Xparam XML;
--Convert @ids to XML 
SELECT @Xparam = CAST('<i>' + REPLACE(@ids,',','</i><i>') + '</i>' AS XML)

--Query to compare the id against the ids result set by splitting the XML nodes
-- as a result set
SELECT * FROM Yourtable WHERE Id IN (SELECT x.i.value('.','INT') FROM @Xparam.nodes('//i') x(i))

谢谢..

Thank you..

这篇关于SQL-ID在哪里(ID列表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！