Sql Server Building query from variables

Question

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'

• Snowlockk: CAST(@statval AS VARCHAR) from here + '+@statval+''

Answer 1

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.

• Pரதீப்: This should be marked as answer since it address not just the op's requirement but also avoids sql injection

Answer 2

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))

• GSerg: And if your SQL Server is new enough, use concat.

Answer 3

Two solutions:

• Cast your statval to varchar as pointed by Andy
• or, If you have the ability, you can declare your statval directly as a varchar