在SQL中将字符串列表转换为Int列表 [英] 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)

，但状态列表现在包含字符串值不是int， into INT？

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

UPDate：

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
SET FMTONLY OFF;
DECLARE @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,',').

strong>（'，'+ @ List +'，'）。

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

推荐答案

列表到使用XML参数的存储过程。这样，你不必再解决这个问题，它是一个更好，更干净的解决方案。

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 * fnStringList2Table(@statuslist))

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