sql-server add tag
user11497433 (imported from SE)

    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
meme (imported from SE)
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

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.