Ravi Sargam Ответов: 3

К TSQL запросов : передача параметров в СП в п.

У меня есть sp, где мне нужно передать параметр, который используется в предложении IN

ниже запрос работает нормально

select name from tbl where Id='001' and locationId IN (1,2,3))


но этот запрос дает ошибку

Declare @var NVARCHAR(100) = '1,2,3';
Declare @whereClause NVARCHAR(100);
SET @whereClause = 'where Id='001' and locationId IN ('+@Var+')';
Select name from tbl + @whereClause


Это ошибка, которую я получаю
Ошибка преобразования при преобразовании значения nvarchar в (1,2,3) ' В тип данных int.

пожалуйста, помогите мне разобраться в том, где я допустил ошибку


заранее спасибо за помощь

Что я уже пробовал:

код, который я пытался использовать 

Declare @var NVARCHAR(100) = '1,2,3';
Select name from tbl where Id='001' and locationId IN (@var)

3 Ответов

Рейтинг:
7

Member 13915320

Вы не можете использовать @var в инструкции IN, если только это не динамический SQL.

Declare @var NVARCHAR(100) = '1,2,3';
declare @sql varchar(300) = 'SELECT * FROM TBL WHERE ID IN (' + @var + ');';
exec(@sql)


Orelse вы можете использовать функцию split строки для разделения @var и использовать непосредственно.

Ravi Sargam

спасибо Вам за ответ u r, я просто сделал глупую ошибку, я забыл об использовании exec(@sql)

CHill60

Я опубликовал это за 17 часов до того, как этот участник опубликовал свое решение после того, как вы прокомментировали, что мое первоначальное решение не сработало!

Ravi Sargam

оба же @CHill60 , u r ответ alos принят спасибо Вам за ответ..... :)

Рейтинг:
27

CHill60

Если вы собираетесь попробовать это таким образом, вам нужно использовать динамический SQL.

Взгляните на решения и ссылки, размещенные на этом посту CP ... Как передать массив строк в SQL параметр в предложении в SQL[^] для выбора решений

[РЕДАКТИРОВАТЬ] Вы не можете смешивать и сопоставлять sql и динамический sql так, как вы пытались.
Измените то, что вам нужно 

Declare @var NVARCHAR(100) = '1,2,3';
Declare @whereClause NVARCHAR(100);
SET @whereClause = 'where Id=''001'' and locationId IN (' + @Var + ')';

Declare @sql nvarchar(max) = 'Select name from tbl '+ @whereClause

print @sql -- Note this prints "Select name from tbl where Id='001' and locationId IN (1,2,3)"

EXEC sp_executesql @sql
Есть лучшие примеры в ссылке, которую я дал выше

Ravi Sargam

да, с динамическим sql я получаю ошибку, если я выполняю обычный запрос, я могу получить точные результаты.

CHill60

Вы не публиковали никаких динамических sql

Ravi Sargam

решение, которое дало это, не работает для меня

Рейтинг:
0

MadMyche

Самый простой способ сделать это-использовать функцию SQL SPLIT в вашем операторе. Эта функция была добавлена вместе с SQL Server 2016, предыдущие версии требовали Определяемая Пользователем Функция чтобы его установили. Если это так то есть много доступных на таких сайтах как Sql Server Central
хорошо читать: Таблица "числа" или "подсчет": что это такое и как она заменяет цикл[^]

Этот оператор использует встроенную функцию 2016+ :

DECLARE @ID NVARCHAR(3) = '001'
DECLARE @LocationIDs NVARCHAR(100) = '1,2,3'

SELECT Name
FROM   Table
WHERE  Id = @ID
AND    LocationID IN (SELECT value FROM STRING_SPLIT(@LocationIDs, ','))


Второй вариант немного сложнее, требуя пользовательских типов SQL, а также другого синтаксиса в вызове ADO. Преимущество заключается в том, что вы можете передавать более сложные типы, такие как DataTables, DataReaders или iEnumerables

Часть SQL
CREATE TYPE dbo.UserTableType_INTs AS TABLE( IntValue INT NOT NULL)
GO
CREATE PROCEDURE DBO.usp_MyProcedure (
  @ID          NVARCHAR(100),
  @LocationIDs dbo.UserTableType_INTs READONLY
) AS
BEGIN
  SELECT Name
  FROM   Table
  WHERE  Id = @ID
  AND    LocationID IN @LocationIDs
END
GO


И вызывая его через ADO в C#
// Sample datatable, I generally don't do this and my syntax may be off
DataTable LocationIDs = new DataTable();
LocationIDs.Clear();
LocationIDs.Columns.Add("IntValue");
LocationIDs.Rows.Add(1);
LocationIDs.Rows.Add(2);
LocationIDs.Rows.Add(3);

SqlCommand cmd = new SqlCommand(sqlInsert, connection);
cmd.CommandType = CommandType.StoredProcedure;

// standard input parameter shortcut
cmd.Parameters.AddWithValue("@ID", "001"); 

// different parameter addition when using an SQL User Type
SqlParameter tvpParam = cmd.Parameters.AddWithValue("@LocationIDs", LocationIDs); 
tvpParam.SqlDbType = SqlDbType.Structured;  
tvpParam.TypeName = "dbo.UserTableType_INTs";

CHill60

Вы уверены в этом первом примере (STRING_SPLIT)? Помимо проблемы двойной кавычки/одинарной кавычки, не должно ли это быть 

SELECT Name 
FROM   Table
WHERE  Id = @ID
AND    LocationID IN (SELECT value FROM STRING_SPLIT (@LocationIDs, ','))

MadMyche

Да, могу поклясться, что у меня там было это; спасибо, ответ обновлен

CHill60

:большой палец вверх: