3

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?

13

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

6

The issue is down to data type precedence.

In this case, the INT data type is of higher precedence, as such the VARCHAR datatype is converted (or attempted to be converted) in this case to the data type of higher precedence (INT).

A simple CAST to VARCHAR should solve this as below

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

SET @stringsvar = ('select * from' + ' ' + QUOTENAME(@strvar) + ' where emp_id' + ' =' + CAST(@Emp_id AS VARCHAR(11)))
Print @stringsvar

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.