perform a operation in stored procedure 1

[AndreAraujo]

Hi, i´m having some problems in a stored procedure.
i need to calculate a numeric value @parametro

declare @parametro numeric (11,0) , @ano int
select @parametro=max(id) from tbl__cabecalho where id_lab=1

set @ano= (@parametro/10000000 )

if @ano = year(getdate())
	begin
		set @parametro =@parametro +1
	end
else
	begin
		set @parametro=( ( (year(getdate()) +1 ) * 10000000) + (@id_laboratorio*1000000) + 1 )

	end

i get Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int error when the stored procedure as to execute the calculation.

Any ideas ????

Thanks in advance

 

[vongrunt]

Yup... add ".0" after 10000000

 

[SQLSister]

let me ask do you want an int result? then make the datatype of @parametro into an int.

Questions about posting. See faq183-874

 

[donutman]

It's too large for int, but not bigint. But isn't anyone curious about what this code is doing. I'll bet there's a better way to accomplish the desired result.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Four digits year, one digit lab ID, six digits counter - probably some kind of redundant composed "talking" code/sequence typically used for EMR/medical apps.

 

[AndreAraujo]

Problem solved, thank vongrunt, the problem is that i need the @parametro to perform an insert into a other table.
and i have to respect this code/sequence format, because the original app uses this format year+lab+counter.

Andre