I'm trying to concatenate the string with multiple variables and go for exec. Unfortunately I'm facing the conversion problem as:
Conversion failed when converting the varchar value 'Select @ExistingIds= CBSE_IX_J from ##tempPivot where EmployeeID=' to data type int.
My query is :
SET @ExecColumn = concat('Select @ExistingIds= '+@TSectionName +' from ##tempPivot where EmployeeID='+CAST(@TUserID as INT),'')
PRINT @ExecColumn
EXEC (@ExecColumn)
The "simple" answer is don't concatenate raw string values into your dynamic statement, and parametrise your code. This is a bit of guesswork, however, is far safer than the SQL Injection hole you have right now:
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;
Note: the fact that your variable is called @ExistingIDs implies you want to store multiple values in that variable. @ExistingIDs is a scalar value, it will only hold a scalar (single) value. If the query above returns multiple rows, only the value of from the last row will be returned. For example:
DECLARE @i int;
SELECT @i = I
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9))V(I)
ORDER BY I;
SELECT @i;
Notice that @i has the value 9, not '1,2,3,...,9'.
You would seem to want:
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;
In older versions of SQL Server, you need another approach for concatenating strings. For instance
SET @SQL = N'
SELECT @ExistingIDs = (SELECT @TSectionName
FROM ##tempPivot
WHERE EmployeeID = @TUserID
FOR XML PATH ('')
)
';
CAST(@TUserID as INT)=>CAST(@TUserID as VARCHAR(20). Beside of that it is not clear why theCONCAT()was used in your snippet