T-SQL problem with a sp

[pap]

Hello,

Can you please help me with this:

I am trying to select a value from a table but in this table I have 25 fields of the same kind so I need to verify the value of each field. To do this I have used a 'while' condition the problem is that it gives me always the same error: Arithmetic overflow error converting varchar to data type numeric

Can you see why ????

Here is the code


set @cursor=1
while @cursor<26
begin
if (@cursor<10)
begin
set @var_siz='pos0' + convert(varchar(2), @cursor) + ' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007
set @var_qtd='poq0' + convert(varchar(2), @cursor) + ' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007
end
else
begin
set @var_siz='pos' + convert(varchar(2), @cursor) + ' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007
set @var_qtd='poq' + convert(varchar(2), @cursor) + ' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007
end

select @size_PO= @var_siz
select @qtd_PO= @var_qtd


Thank you,
Pap

[skhanal]

what are the datatypes of your variables. sql server does implicit conversion of varchar to int when you do arithmetic with int variable. And the varchar variable may have invalid value to convert.

[pap]

My variable data types are

@var_siz varchar(500)
@var_qtd varchar(500)

@size_PO varchar(2)
@qtd_PO numeric(5,1)

[skhanal]

The problem is this statement

select @qtd_PO= @var_qtd

@qtd_PO is numeric and @var_qtd is varchar with 'poq0' or 'poq' as prefix. Change @qtd_po to varchar.