or
sql-server
user11497433 imported from SE
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?
Top Answer
Erik Darling
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
Dynamic SQL query - how do I add an int to the code?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.