Dynamic SQL query - how do I add an int to the code?

Consider:

Declare @stringsvar varchar(1000)
Declare @Emp_id int 
DECLARE  @strvar SYSNAME = 'Employee_test'

SET @stringsvar = ('select * from' + ' ' + @strvar + ' where emp_id' + ' =' + @Emp_id)
Print @stringsvar

The above query is giving an error as mentioned below:


Msg 245, Level 16, State 1, Line 17 Conversion failed when converting
the nvarchar value 'select * from Employee_test where emp_id =' to
data type int.

What needs to be done in this case?

George's answer does solve your problem, but it leaves you wide open to SQL injection attacks.

While converting an INT to a VARCHAR 11 is likely not going to cause any issues, sysname is the equivalent of an NVARCHAR 128, and you can jam a lot of extra code in there.

To make your code totally safe, you'd want to do this:

DECLARE @stringsvar NVARCHAR(1000) = '';
DECLARE @Emp_id INT = 1;
DECLARE @strvar sysname = N'Employee_test';

SET @stringsvar = ( N'select * from ' 
                    + QUOTENAME(@strvar) 
                    + N' where emp_id' 
                    + N' = @iEmp_id' );

PRINT @stringsvar;
EXEC sys.sp_executesql @stringsvar, 
                       N'@iEmp_id INT', 
                       @iEmp_id = @Emp_id;

Using sp_executesql to issue parameterized dynamic SQL, and quotename to make the table name non-executable code is a much safer choice.

For a little more reference, I'd suggest heading over here: The Curse and Blessings of Dynamic SQL

StackExchange Database Administrators Q#241607, answer score: 13