CODEWITHSUNDEEP
t-sqlsql-server-2012windows-server-2012
Ganjira
Ganjira

Reputation: 976

Conversion failed when converting the varchar. Working with cursors from t-sql

I am using T-SQL and cursors to list the emp database.

Here's my code:

DECLARE cursors CURSOR FOR 
   SELECT 
       emp.Ename, emp.Sal 
   FROM Emp 
   WHERE Sal > 200

DECLARE @surname VARCHAR(50), @salary INT

PRINT 'Employees earning more than 200:'

OPEN cursors
FETCH NEXT FROM cursors INTO @surname, @salary

WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT @surname + ' ' + @salary
        FETCH NEXT FROM cursors INTO @surname, @salary
END

CLOSE cursors
DEALLOCATE cursors

The problem is that when I try to execute the code I am getting this message:

Employees earning more than 200: Msg 245, Level 16, State 1, Line 8
Conversion failed when converting the varchar value 'SMITH ' to data type int.

Did I do something wrong? How to solve it?

Upvotes: 0

Views: 1848

Answers (2)

John Odom
John Odom

Reputation: 1223

You are trying to add an integer to a string in this line: PRINT @surname + ' ' + @salary

The way to fix this is to convert the @salary on that line to varchar with CONVERT or CAST Here is a way to do it with CONVERT:

PRINT @surname + ' ' + CONVERT(varchar(100), @salary)

Here is a way to convert the int to varchar with CAST:

PRINT @surname + ' ' + CAST(@salary AS varchar(100))

Be sure to set the number in varchar to accommodate for the maximum number of digits that @salary will have, or else you will get this error:

Arithmetic overflow error converting numeric to data type varchar.

As to why it is complaining about SMITH being converted to int it is because of Data Type Precedence. Data types with lower precedence tries to get converted to data types of higher precedence. varchar has a lower precedence than int, hence SMITH trying to be converted to an int.

Upvotes: 3

Reisclef
Reisclef

Reputation: 2148

I think you may need to convert/cast @salary to a varchar to print it:

 PRINT @surname + ' ' + convert(varchar(10),@salary)

Upvotes: 2

Related Questions