BorlandTalk.com

Bruno Lovatti · Guest

look at this code:

CREATE PROCEDURE BRU_MANTEMCHAVE_SI2 AS
BEGIN TRANSACTION

declare
@tabela varchar(6),
@dc varchar(1),
@ychave varchar(24),
@sql nvarchar(500),
@hisrecno int,
@recno int,
@recnopai int -- RecNo temporário

exec('declare tab_cursor cursor for select name from sysobjects where name
like ''SI2___''')

open tab_cursor

fetch next from tab_cursor
into @tabela
while @@FETCH_STATUS = 0
begin
-- pega o primeiro RecNo que é complemento de histórico e não tem chave
set @sql = N'select @hisrecno = min(R_E_C_N_O_) from '
+ @tabela +' where D_E_L_E_T_='''' and I2_DC=''-'' and
I2_YCHAVE='''''
exec sp_executesql @sql, N'@hisrecno int output', @hisrecno output

-- se for null não preciso fazer nada
if @hisrecno is not null
begin
select @tabela, @hisrecno

-- pega o pai do primeiro RecNo que é complemento de histório
set @sql = N'select @recnopai = max(R_E_C_N_O_) from '
+ @tabela +' where D_E_L_E_T_='''' and
I2_DC<>''-'' and R_E_C_N_O_ <'+str(@hisrecno)
exec sp_executesql @sql, N'@recnopai int output', @recnopai output

-- inicia a atualização a partir do pai do RecNo com complemento
exec( 'declare si2_cursor cursor for select I2_DC,I2_YCHAVE,R_E_C_N_O_
from '
+@tabela+' where D_E_L_E_T_='''' and
R_E_C_N_O_>='+@recnopai+' order by R_E_C_N_O_' )

open si2_cursor

fetch next from si2_cursor
into @dc, @ychave, @recno

while @@FETCH_STATUS = 0
begin
if @dc='-'
begin
-- atualiza o campo ychave
exec( 'update ' + @tabela + ' set I2_YCHAVE = ltrim(rtrim( str('
+ @recnopai + ') )) where current of
si2_cursor' )
end
else
begin
-- grava o último recno
set @recnopai = @recno
end

fetch next from si2_cursor
into @dc, @ychave, @recno
end

close si2_cursor
deallocate si2_cursor

end -- fim if not(@minrecno is null)

fetch next from tab_cursor
into @tabela
end

close tab_cursor
deallocate tab_cursor

COMMIT
GO

this code works now, but if i change

set @sql = N'select @recnopai = max(R_E_C_N_O_) from '
+ @tabela +' where D_E_L_E_T_='''' and I2_DC<>''-'' and
R_E_C_N_O_ <'+str(@hisrecno)
to
set @sql = N'select @recnopai = max(R_E_C_N_O_) from '
+ @tabela +' where D_E_L_E_T_='''' and I2_DC<>''-'' and
R_E_C_N_O_ <'+@hisrecno

the code doesn't work, i only cut str()

why? why? why can't i remove the str if it works in the exec with @recnopai
(variable of same type of @hisrecno)?
and if i change the line

exec( 'declare si2_cursor cursor for select I2_DC,I2_YCHAVE,R_E_C_N_O_
from '
+@tabela+' where D_E_L_E_T_='''' and
R_E_C_N_O_>='+@recnopai+' order by R_E_C_N_O_' )
to
exec( 'declare si2_cursor cursor for select I2_DC,I2_YCHAVE,R_E_C_N_O_
from '
+@tabela+' where D_E_L_E_T_='''' and
R_E_C_N_O_>='+str(@recnopai)+' order by R_E_C_N_O_' )
i get design error, i only added str()

i can't understand.

thank you!

Bruno

Anton Santa · Guest

try convert instead of STR()
Toni

"Bruno Lovatti" <blovatti(remove this)@terra.com.br> ha scritto nel
messaggio news:40fd91ab (AT) newsgroups (DOT) borland.com...

Bruno Lovatti · Guest

thanks!
the code was working already, i'm only thinking why i can concatenate the
string without str in the exec command and i can't in sp_executesql