Как передать строковый параметр с оператором IN в хранимой процедуре SQL Server 2008

У меня есть хранимая процедура, когда я ее выполняю, я получаю ошибку

Ошибка преобразования при преобразовании значения varchar '+@dptId+' в тип данных int

Я получаю DepartmentId в виде строки типа (1,3,5,77) и передаю ее в свою хранимую процедуру.

SQL FIDDLE

create table dummy (id int,name varchar(100),DateJoining Datetime, departmentIt int)

insert into dummy values (1,'John','2012-06-01 09:55:57.257',1);
insert into dummy  values(2,'Amit','2013-06-01 09:55:57.257',2);
insert into dummy  values(3,'Naval','2012-05-01 09:55:57.257',3);
insert into dummy  values(4,'Pamela','2012-06-01 09:55:57.257',4);
insert into dummy  values(5,'Andrea','2012-09-01 09:55:57.257',3);
insert into dummy  values(6,'Vicky','2012-04-01 09:55:57.257',4);
insert into dummy  values(7,'Billa','2012-02-01 09:55:57.257',4);
insert into dummy  values(8,'Reza','2012-04-01 09:55:57.257',3);
insert into dummy  values (9,'Jacob','2011-05-01 09:55:57.257',5);

Запрос, который я пробовал:

declare @startdate1 varchar(100) ='20120201'
declare @enddate1 varchar(100)='20130601'
declare @dptId varchar(100)='3,4'

select * 
from dummy
where DateJoining >= @startdate1 and DateJoining < @enddate1 
  and departmentIt IN (@dptId);

stored-procedures sql-server-2008-r2 parameterized
person Satinder singh    schedule 01.06.2013    source источник
comment
IN так не работает. Как насчет departmentIt = @dptId1 or departmentIt = @dptId2;?   -  person juergen d    schedule 01.06.2013
comment
Раньше я использовал простую строку запроса, такую ​​​​как departmentIt in (1,3), и она работает, теперь, когда я использую параметризованный запрос, я получаю сообщение об ошибке.   -  person Satinder singh    schedule 01.06.2013
comment
departmentIt in (1,3) работает, а departmentIt in ('1,3') нет, так как это всего лишь значение 1 — строка, а не 2 числа.   -  person juergen d    schedule 01.06.2013
comment
@juergend: да, я понимаю, что это ошибка, но это также невозможно сделать с помощью or, потому что я получаю значения @dptId в виде строки   -  person Satinder singh    schedule 01.06.2013
comment
Пожалуйста, поищите еще немного, так как это не редкий вопрос. Это можно сделать с помощью динамического SQL. Попробуйте Google с помощью: sql dynamic "in" comma separated   -  person HABO    schedule 01.06.2013

Ответы (4)


arrow_upward
23
arrow_downward

Вот как я это решил: Рабочий скрипт SQL

Сначала я создал функцию, которая разбивает строковое значение, т.е. '1,2,4,5'

Функция разделения:

CREATE  FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)  
)
AS
BEGIN

DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END

Позже в моем запросе я использую эту функцию разделения

declare @startdate1 varchar(100) ='20120201'
declare @enddate1 varchar(100)='20130601'
declare @dptId varchar(100)='3,4'

select * from dummy
where DateJoining >=@startdate1 and DateJoining < @enddate1 
  and departmentID IN (SELECT Value FROM fn_Split(@dptId, ','));
person Satinder singh    schedule 01.06.2013
comment
@Сантиндер Сингх открыт для SQL-инъекций? - person quinekxi; 09.12.2013

arrow_upward
4
arrow_downward

Попробуйте использовать sp_executesql в качестве ответа. Не самый эффективный, но работает

ALTER PROCEDURE [dbo].[uspTestReportData_GetBySerial] 
    @SerialNumbers nvarchar(200)
AS
BEGIN
    SET NOCOUNT ON;
    declare @sql nvarchar(200)

    set @sql = 'SELECT * from MyTable WHERE  Serial_Number in (' + @SerialNumbers + ')'
    execute sp_executesql @sql                    

END
person Pierrie Fields    schedule 12.07.2013
comment
Привет, это небезопасно, так как кто-то может ввести опасное предложение sql. - person code5; 08.12.2015

arrow_upward
2
arrow_downward

Просто вы можете сделать следующее SELECT:

SELECT M.REG_NO, T.TYPE_ID 
    FROM MAIN AS M 
        INNER JOIN CLASSIFICATION AS C 
            ON M.REG_NO = C.REG_NO
        INNER JOIN TYPE AS T 
            ON T.TYPE_ID = C.TYPE_ID
    WHERE (','+@Types+',') LIKE '%,' +T.TYPE_ID+ ',%'
person Mike Darwish    schedule 29.09.2016
comment
Это решение, которое я бы использовал. Простота является ключевым фактором. - person Paul; 08.11.2017

arrow_upward

arrow_downward

person    schedule
comment
Вы должны добавить объяснение того, как это решит проблему. - person AndroidMechanic - Viral Patel; 13.01.2016