Блог

Главная - Вопросы по программированию - Проблема с конкатенацией в динамическом sql-запросе

Проблема с конкатенацией в динамическом sql-запросе

#sql #sql-server #tsql

#sql #sql-сервер #tsql

Вопрос:

Я пытаюсь объединить строку с несколькими переменными и перейти к exec. К сожалению, я сталкиваюсь с проблемой преобразования как:

Произошел сбой преобразования при преобразовании значения переменной ‘Select @ExistingIds= CBSE_IX_J from ##tempPivot where EmployeeID=’ в тип данных int.

Мой запрос :

 SET @ExecColumn = concat('Select @ExistingIds= ' @TSectionName  ' from ##tempPivot where EmployeeID=' CAST(@TUserID as INT),'')
PRINT @ExecColumn
EXEC (@ExecColumn)

Комментарии:

1. CAST(@TUserID as INT) => CAST(@TUserID as VARCHAR(20) . Кроме того, неясно, почему CONCAT() был использован в вашем фрагменте

2. В чем смысл этого запроса? Нет причин использовать конкатенацию строк для @TUserID. Выполнение этого запроса также завершится неудачей, поскольку параметры и переменные должны быть определены в области, в которой они используются — в самом запросе

Ответ №1:

«Простой» ответ заключается в том, что не объединяйте необработанные строковые значения в свой динамический оператор и параметризуйте свой код. Однако это немного наугад, гораздо безопаснее, чем дыра в SQL-инъекциях, которая у вас есть прямо сейчас:

 DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT @ExistingIDs = '   QUOTENAME(@TSectionName)   NCHAR(13)   NCHAR(10) 
           N'FROM ##tempPivot'   NCHAR(13)   NCHAR(10)  
           N'WHERE EmployeeID = @TUserID;';
PRINT @SQL;
EXEC sp_executesql @SQL,
                   N'@TUserID int, @ExistingIds int OUTPUT', --guessed datatypes and that @ExistingIds is an OUTPUT
                   @TUserID = @TUserID,
                   @ExistingIds = ExistingIds OUTPUT;

Примечание: тот факт, что ваша переменная вызывается, @ExistingIDs подразумевает, что вы хотите сохранить несколько значений в этой переменной. @ExistingIDs является скалярным значением, оно будет содержать только скалярное (одиночное) значение. Если приведенный выше запрос возвращает несколько строк, будет возвращено только значение из последней строки. Например:

 DECLARE @i int;
SELECT @i = I
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9))V(I)
ORDER BY I;
SELECT @i;

Обратите внимание, что @i имеет значение 9 , а не '1,2,3,...,9' .

Ответ №2:

Казалось бы, вы хотите:

 DECLARE @SQL nvarchar(MAX);
DECALRE @ExistingIds NVARCHAR(MAX);

SET @SQL = N'
SELECT @ExistingIDs = STRING_AGG(@TSectionName, '''')
FROM ##tempPivot
WHERE EmployeeID = @TUserID
';

-- Cannot have identifiers as parameters, so use `REPLACE()`
SET @SQL = REPLACE(@SQL, '@TSectionName', QUOTENAME(@TSectionName);

EXEC sp_executesql @SQL,
                   N'@TUserID int, @ExistingIds NVARCHAR(MAX) OUTPUT', --guessed datatypes and that @ExistingIds is an OUTPUT
                   @TUserID=@TUserID,
                   @ExistingIds=@ExistingIds OUTPUT;

В более старых версиях SQL Server вам нужен другой подход к объединению строк. Например

 SET @SQL = N'
SELECT @ExistingIDs = (SELECT @TSectionName
                       FROM ##tempPivot
                       WHERE EmployeeID = @TUserID
                       FOR XML PATH ('')
                      )
';