SQLTeam.com | Weblogs | Forums

Data conversion problem Decimal to Integer when assigning to a variable


#1

Hi all
I am busy converting data from one DB to another.
Part of the process is to create a record in the NEW table for each record that has existed in the OLD table (even if that record has been deleted from the old table) [Weird I know - don't ask!]

the OLD table has a STUDID field that is a decimal (8,0) (SQL 2005)
the NEW table is using an integer for the ID_Student field. (SQL 2012)

In order to ensure that a record is created for each OLD student, I am trying to assign the value of MAX(StudentID) from the OLD table and loop through an insert cycle to create a dummy record for each student. (True data is updated by a series of update queries later)

So to start the loop, I have tried
DECLARE @intFlag INT
SET @intFlag = 'SELECT CAST(MAX(STUDID) AS integer) FROM OLD_TBL_STUDENT'
-- check that a value has been assigned
PRINT 'INTFLAG = ' + @IntFlag

The 'SELECT CAST(MAX(STUDID) AS integer) FROM OLD_TBL_STUDENT' part runs just fine as a stand-alone command, but I get errors

So after numerous tests, I eventually tried
DECLARE @MyVal INTEGER
set @MyVal = 1
print 'MyVal = ' + @MyVal

and found that the first 2 lines work OK....HOWEVER, when I try to include the PRINT statement, I run into a
'Conversion failed when converting the varchar value 'MyVal = ' to data type int.' issue
This error implies that even though I declared the variable as an integer, the system is assigning it as type varchar. So what's happening here?

How can I assign the value to my @intFlag variable so that I can loop through the process accordingly.

Thanks


#2

You are concatenating the integer value with string. It should be written as below
print 'MyVal = ' + CAST(@MyVal as VARCHAR(1))