declare @String varchar(max),
@Stat varchar(3),
@Statval int
SET @Stat = 'STR'
SET @Statval = 500
set @String = 'UPDATE [Addpoints] SET ['+@stat+'] = ['+@stat+'] + '+@statval+''
SELECT @String
I'm trying to build query inside @String from some variables. But i'm getting error - 'Conversion failed when converting the varchar value 'UPDATE [Addpoints] SET ' to data type int.'
At the end i want @String to be like this
@String = 'Update [Addpoints] SET [STR] = [STR] + 500'
Your query would not be safe enough. Use this instead:
DECLARE @String NVARCHAR(MAX)
, @Stat VARCHAR(3) = 'STR'
, @Statval INT = 500;
SET @String = N'
UPDATE A
SET A.' + QUOTENAME(@Stat) + N' += @Statval
-- SELECT *
FROM dbo.Addpoints AS A;';
PRINT @String;
EXECUTE sys.sp_executesql @SQL, N'@Statval INT', @Statval;
This query will generate following:
UPDATE A
SET A.[STR] += @Statval
-- SELECT *
FROM dbo.Addpoints AS A;
Keep in mind that [STR] = [STR] + 500 is exactly the same as [STR] += @Statval, it's just a nice shorthand.
As you can see column names are quoted. QUOTENAME() does that for you and escapes illegal characters. And sp_executesql let's you create parameterized queries, which are safe and their execution plans are cached.
In your case @Statval will be assigned a value of 500.
You have to cast int values to varchar ones explicitly when you're combining them into string:
set @String = 'UPDATE [Addpoints] SET [' + @stat + '] = [' + @stat + '] + ' +
cast(@statval as varchar(max))
Two solutions:
