Динамический SQL-запрос с разделителями-запятыми

Question

[Обновление: Использование SQL Server 2005]

Привет, что я хочу сделать, это запросить мою хранимую процедуру с разделенным запятыми списком значений (идентификаторов), чтобы получить строки данных.

Проблема, которую я получаю, - ошибка преобразования:

Conversion failed when converting the varchar value ' +
@PassedInIDs + ' to data type int.

Утверждение в моем предложении where и error:

...
AND (database.ID IN (' + @PassedInIDs + '))

Примечание: database.ID имеет тип int.

Я следил за статьей по адресу:

http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx

но не завершено из-за ошибки.

В моем сценарии выполнения у меня есть:

...
@PassedInIDs= '1,5'

Я что-то здесь не так делаю? Спасибо за помощь.

Answer 1

Я хотел бы создать табличную функцию CLR:

http://msdn.microsoft.com/en-us/library/ms131103.aspx

В нем вы разберете строку на части и выполните преобразование в набор строк. Затем вы можете присоединиться к результатам этой таблицы или использовать IN, чтобы увидеть, есть ли идентификатор в списке.

Answer 2

Например, вы передаете строку в функцию IN в SQL. Если вы оглянетесь на исходную статью, то увидите, что вместо прямого оператора SQL вместо этого создается string который является оператором SQL для выполнения.

Answer 3

В SQL нет строковой оценки. Это:

database.ID IN (' + @PassedInIDs + ')

не будет обращено к:

database.ID IN (1,2,3)

только потому, что @PassedInIDs параметр содержит '1,2,3', The parameter is not even looked at, because all you have is a string containing " + @PassedInIDs + ", Syntactically, this is equivalent to:

database.ID IN ('Bob')

To make it short, you can't do what you attempt here in SQL. But there are four other possibilities:

1. вы создаете строку SQL на языке вызова и вообще отказываетесь от хранимой процедуры
2. вы используете динамический подготовленный оператор с таким количеством параметров в предложении IN, сколько вы собираетесь использовать
3. Вы используете фиксированный подготовленный оператор, скажем, с 10 параметрами: IN (?,?,?,?,?,?,?,?,?,?), заполняя столько, сколько вам нужно, устанавливая остальные в NULL
4. вы создаете хранимую процедуру, скажем, с 10 параметрами и передаете столько, сколько вам нужно, устанавливая остальные в NULL: IN (@p1, @p2, ..., @p10),

Answer 4

Я настоятельно рекомендую вам использовать второй метод из этой ссылки. Создайте пользовательскую функцию, которая превращает вашу строку с разделителями-запятыми в таблицу, которую вы можете легко выбрать.

Если вы используете Google на Erland и "Динамический SQL", у него есть хорошее описание ловушек, которые это влечет за собой.

Answer 5

Вам нужно обращаться с ufn_CSVToTable как с таблицей. Таким образом, вы можете присоединиться к функции:

JOIN ufn_CSVToTable(@PassedInIDs) uf ON database.ID = uf.[String]

Answer 6

      Try this:

    DECLARE @Ids varchar(50);
    SET @Ids = '1,2,3,5,4,6,7,98,234';
    
    SELECT * 
    FROM sometable 
    WHERE ','+@Ids+',' LIKE '%,'+CONVERT(VARCHAR(50),tableid)+',%';

Answer 7

Это может быть решено 6 способами, как упомянуто в статье Нараяны. Передача списка / массива в хранимую процедуру SQL Server.

И моя самая прямолинейная реализация

объявить @statement nvarchar(256)
set @statement = 'select * from Persons, где Persons.id в (' + @PassedInIDs + ')'
exec sp_executesql @statement

-

Answer 8

Вот что я нашел и протестировал:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE  FUNCTION [dbo].[SplitStrings] ( @IDsList VARCHAR(MAX) )
RETURNS @IDsTable TABLE ( [ID] VARCHAR(MAX) )
AS 
BEGIN
    DECLARE @ID VARCHAR(MAX)
    DECLARE @Pos VARCHAR(MAX)

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

    IF REPLACE(@IDsList, ',', '') <> '' 
        BEGIN
            WHILE @Pos > 0 
                BEGIN
                    SET @ID = LTRIM(RTRIM(LEFT(@IDsList, @Pos - 1)))
                    IF @ID <> '' 
                        BEGIN
                            INSERT  INTO @IDsTable
                                    ( [ID] )
                            VALUES  ( CAST(@ID AS VARCHAR) )
                        END
                    SET @IDsList = RIGHT(@IDsList, LEN(@IDsList) - @Pos)
                    SET @Pos = CHARINDEX(',', @IDsList, 1)
                END
        END 
    RETURN
END

GO

Вот как функция Call:

SELECT * FROM dbo.SplitStrings('123,548,198,547,965')

Answer 9

Я предлагаю использовать XML для этого в SQL 2005. Несколько объемнее, но это может быть проще. Это позволяет вам выбрать XML в таблицу, которую затем можно соединить или вставить и т. Д.

Посмотрите на OPENXML() сервера SQL, если вы еще этого не сделали.

Например, вы можете передать что-то вроде: "12..."

а затем используйте:

exec sp_xml_preparedocument @doc OUTPUT, @xmlParam

SELECT element 
FROM OPENXML (@doc, 'Array/Value', 2) WITH (element varchar(max) 'text()')

Это должно быть началом