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?
You need to be careful about [SQL injection attacks][2].
While converting an `INT` to a `VARCHAR 11` is likely not going to cause any issues, [sysname][3] 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][4] to issue parameterized dynamic SQL, and [quotename][5] 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][6]
[2]: https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/
[3]: https://stackoverflow.com/questions/5720212/what-is-sysname-data-type-in-sql-server
[4]: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017
[5]: https://docs.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql?view=sql-server-2017
[6]: http://www.sommarskog.se/dynamic_sql.html
