SQL中将字符串列表转换为整数列表 [英] Converting String List into Int List in SQL

问题描述

我的存储过程中有一个 nvarchar(MAX)，其中包含 int 值列表，我这样做是因为 不可能将 int 列表传递给我的存储过程，但是，现在我遇到了问题，因为我的数据类型是 int，我想比较字符串列表.有没有办法让我做同样的事情?

I have a nvarchar(MAX) in my stored procedure which contains the list of int values, I did it like this as it is not possible to pass int list to my stored procedure, but, now I am getting problem as my datatype is int and I want to compare the list of string. Is there a way around by which I can do the same?

---myquerry----where status in (@statuslist)

但是状态列表现在包含字符串值而不是整数，那么如何将它们转换为整数?

but the statuslist contains now string values not int, so how to convert them into INT?

更新日期:

USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[SP]
(
@FromDate datetime = 0,
@ToDate datetime = 0,
@ID int=0,
@List nvarchar(MAX) //This is the List which has string ids//
)

AS设置 FMTONLY 关闭；声明@sql nvarchar(MAX),@paramlist nvarchar(MAX)

AS SET FMTONLY OFF; DECLARE @sql nvarchar(MAX), @paramlist nvarchar(MAX)

SET @sql    = 'SELECT ------ and Code in(@xList)
  and -------------'
SELECT @paramlist = '@xFromDate datetime,@xToDate datetime,@xId int,@xList nvarchar(MAX)'

EXEC sp_executesql @sql, @paramlist, 
@xFromDate = @FromDate ,@xToDate=@ToDate,@xId=@ID,@xList=@List
PRINT @sql

因此，当我实现拆分的函数时，我无法指定字符或分隔符，因为它不接受它为 (@List,',').

So when I implement that function that splits then I am not able to specify the charcter or delimiter as it is not accepting it as (@List,',').

或(','+@List+',').

推荐答案

可以使用 XML 参数向存储过程发送 int 列表.这样您就不必再解决这个问题，而且它是一个更好、更干净的解决方案.

It is possible to send an int list to your stored procedure using XML parameters. This way you don't have to tackle this problem anymore and it is a better and more clean solution.

看看这个问题:将参数数组传递给存储过程

或检查此代码项目:http://www.codeproject.com/Articles/20847/Passing-Arrays-in-SQL-Parameters-using-XML-Data-Ty

但是，如果您坚持按照自己的方式进行操作，则可以使用此功能:

However if you insist on doing it your way you could use this function:

CREATE FUNCTION [dbo].[fnStringList2Table]
(
    @List varchar(MAX)
)
RETURNS 
@ParsedList table
(
    item int
)
AS
BEGIN
    DECLARE @item varchar(800), @Pos int

    SET @List = LTRIM(RTRIM(@List))+ ','
    SET @Pos = CHARINDEX(',', @List, 1)

    WHILE @Pos > 0
    BEGIN
        SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
        IF @item <> ''
        BEGIN
            INSERT INTO @ParsedList (item) 
            VALUES (CAST(@item AS int))
        END
        SET @List = RIGHT(@List, LEN(@List) - @Pos)
        SET @Pos = CHARINDEX(',', @List, 1)
    END

    RETURN
END

这样称呼它:

SELECT      *
FROM        Table
WHERE status IN (SELECT * from fnStringList2Table(@statuslist))

这篇关于SQL中将字符串列表转换为整数列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！