July 4, 2013 at 10:15 am
can someone tell why this works in sql2000, but not sql2008?
DECLARE @rows varchar(5), @cnt int, @pak decimal, @results varchar(1000)
SELECT @cnt = isnull(max(seq),0) from @patemp <<< this temp table is empty so @cnt becomes zero
IF @cnt>0 BEGIN
UPDATE unanet..sequence_number SET @pak=last_number=last_number+@cnt
WHERE table_name='project_assignment'
UPDATE @patemp SET pa_key=@pak-@cnt+seq << this worked in sql2000 but gives error in sql2008:
Data type decimal of receiving variable is not equal to the data type decimal of column 'last_number'. [SQLSTATE 42000] (Error 425). The step failed.
sorry, here table:
CREATE TABLE [dbo].[sequence_number](
[table_name] [varchar](50) NOT NULL,
[last_number] [decimal](15, 0) NOT NULL,
July 4, 2013 at 2:14 pm
If you try this on SQL 2000:
CREATE TABLE [dbo].[sequence_number](
[table_name] [varchar](50) NOT NULL,
[last_number] [decimal](15, 0) NOT NULL)
go
INSERT sequence_number(table_name, last_number) VALUES ('project_assignment', 99)
DECLARE @pak decimal(2,0)
UPDATE sequence_number
SET @pak=last_number=last_number+10
WHERE table_name='project_assignment'
SELECT * FROM sequence_number
SELECT @pak AS [@pak]
go
DROP TABLE sequence_number
You will find that you get the error:
Server: Msg 426, Level 16, State 1, Line 5
The length 5 of the receiving variable is less than the
length 9 of the column 'last_number'.
If you change the definition of last_number to decimal(3,0), you instead get:
table_name last_number
-------------------------------------------------- -----------
project_assignment 109
(1 row(s) affected)
@pak
----
109
Which is incorrect, because @pak was declared as decimal(2,0), and thus 109 is not a valid value.
Thus, the error message in SQL 2008 is a generalisation of the error message in SQL 2000, and also, in my opinion a logical error, since in SQL 2000 it worked under some circumstances.
By the way, always explicitly specify scale and precision for decimal variables. Only "decimal" is the same as decimal(18, 0)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply