Stored procedure com While até select ser diferente de nulo ou vazio

[Júnior Guedes 0]

Pessoa bom dia.

 

tenho um select que pretendo que ele seja responsável por gerar um numero de protocolo:

 

select (cast(year(getDate()) as varchar(10)) + cast ( round(100000*rand(),0) as varchar(5))) as NovoNumeroProtocolo

where (cast(year(getDate()) as varchar(10)) + cast ( round(100000*rand(),0) as varchar(5))) not in (select NumeroProtocolo from RecebLote)

Tenho uma tabela com alguns numeros de protocolo a RecebLote. Queria fazer uma stored procedure que executasse esse select até ele conseguir retornar um valor, ou seja, se ele retornar um valor não haverá nenhum numero de protocolo igual ao gerado por ele.

Um outro problema que terei que resolver é que preciso que seja o formato "ANO+5digitos" e nem sempre tenho os 5 dígitos com essa função. Mas isso eu acho que consigo fazer.

Então gostaria de uma stored procedure que fizesse um "While" até esse valor ser diferente de nulo ou vazio.

Fico grato pela ajuda.

[Motta 645]

Alguns cuidados tem de ter tomados para gravações deste tipo :

 

Estar numa "Transaction" inclusive o processo que vai usar o nº de protocolo

Tratar esta quebra de Ano

 

------------------

basicamente :

 

Iniciar transação

select num_protocolo
from protocolo <for update>

somar 1 ao protocolo

update novo_protocolo

Para o 5 dígitos use LPAD

[Júnior Guedes 0]

Olá Motta.. obrigado pela colaboração, com relação à Transaction, estou controlando com uma Transaction aberta na aplicação em dotnet, dentro será chamada essa stored procedure também. Acredito que nao deva causar problemas. Ainda estou decidindo se farei o codigo gerando ano+5digitos sendo os 5 digitos sequenciais ou ano+5digitos sendo os 5 digitos randomicos e verificados no banco a existencia.

 

para o segundo caso eu consegui fazer da seguinte forma:

 

CREATE PROCEDURE GetNumeroProtocolo

AS

SET NOCOUNT ON

declare @p1 varchar(20)

declare @p2 varchar (20)

declare @pr varchar (50)

declare @resultado varchar (530)

set @p1= cast(year(getDate()) as varchar(10))

set @p2 = cast ( round(100000*rand(),0) as varchar(5))

set @pr = Ltrim (@p1 + @p2) + replicate('0', 9 - len((@p1 + @p2)))

set @resultado = (select @pr as NovoNumeroProtocolo where @pr not in (select NumeroProtocolo from RecebLote))

print ISNULL(@resultado,0)

while (ISNULL(@resultado,0)=0)

begin

set @p1= cast(year(getDate()) as varchar(10))

set @p2 = cast ( round(100000*rand(),0) as varchar(5))

set @pr = Ltrim (@p1 + @p2) + replicate('0', 9 - len((@p1 + @p2)))

set @resultado = (select @pr as NovoNumeroProtocolo where @pr not in (select NumeroProtocolo from tiss.RecebLote))

print @resultado

end

return @resultado

Mas ainda nao é certo ficar desta forma, talvez use a soma +1 nos 5 digitos.

[Motta 645]

Este NUmProtocolo ficaria numa tabela com um só registro , não ?

 

Eu faria simples , se não achar no ano cria com 00001, senão soma 1 e grava.

[Júnior Guedes 0]

Nao. É uma tabela com alguns outros campos e vários registros. A questão é que estou trazendo somente o campo NumeroProtocolo e criando um conjunto de numeros de protocolos já inseridos no banco apenas para fazer um where not in (todos os numeros já cadastrados).

 

Mas em conversa com um parceiro aqui, resolvi fazer como você está falando, de forma sequencial (ANO+00001...ANO+99999).

 

Valeu pela ajuda.

[Motta 645]

Faça uma tabela de controle com um registro para guardar o último protocolo , é mais barato que controlar via Max da tabela Operacional

 

Outra coisa , podendo ter "banguelas" na sequencia , e não tendo outros complicadores (ano etc) um Objeto SEQUENCE é bem mais simples de usar.

Gera uma sequencia única, mas não garante integridade sequencial.

[advaldomesquita 93]

Pq nao gera um identity para este campo com formula?

[Motta 645]

Este identity garante unicidade e integridade (sem "banguelas") ?

 

No caso acima parece não poder haver falhas.

[Júnior Guedes 0]

Obrigado Jr e Motta.. como vc falou Motta, eu fiz da seguinte forma: Criei a tabela "Parametros" que será muito útil também para outras configurações. campos: ano_protocolo e ultimo_sequencial_protocolo

 

CREATE PROCEDURE [TISS].[GetNumeroProtocolo]

AS

SET NOCOUNT ON

--*********************************************************************************************************

declare @ultimo_sequencial float

declare @ano_protocolo float

declare @ano_sistema float

set @ultimo_sequencial = (SELECT ( cast(ultimo_sequencial_protocolo as Float)) from tiss.Parametros)

set @ano_protocolo = (SELECT ( cast(ano_protocolo as Float)) from tiss.Parametros)

set @ano_sistema = cast((select cast( year(getDate()) as varchar(4))) as float)

declare @retorno varchar(15)

declare @strSequencial varchar (10)

set @strSequencial = cast((@ultimo_sequencial + 1) as varchar(5))

declare @codigo5 varchar(10)

set @codigo5 = replicate('0',5 - len(@strSequencial)) + ltrim(@strSequencial)

if (@ano_protocolo=@ano_sistema)

begin

set @retorno = (cast(@ano_protocolo as varchar(4) ) + @codigo5)

select @retorno as NovoNumeroProtocolo

end

if (@ano_protocolo < @ano_sistema)

begin

update tiss.parametros set ano_protocolo=cast(@ano_sistema as varchar(4)), ultimo_sequencial_protocolo='00001'

set @retorno = (cast(@ano_sistema as varchar(4) ) + '00001')

select @retorno as NovoNumeroProtocolo

end

if (@ano_sistema < @ano_protocolo)

begin

set @retorno = '0'

select @retorno as NovoNumeroProtocolo

end

print @retorno

return @retorno

Estou ainda analisando para ver se pode haver alguma inconsistência.

ahh sim.. faltou a linha em negrito abaixo para atualização do sequencial:

 

(...)

if (@ano_protocolo=@ano_sistema)

begin

set @retorno = (cast(@ano_protocolo as varchar(4) ) + @codigo5)

select @retorno as NovoNumeroProtocolo

update tiss.parametros set ultimo_sequencial_protocolo=@codigo5

end

(...)

[Motta 645]

A ideia de uma tabela de parâmetros é boa, mas cuidado que se ela for muito acessada e sendo lockada por SELECT FOR UPDATE, pode criar um gargalo na aplicação com muitos processos demandando por atualizá-la.

 

Exemplo : Um usuário está mudando a referência contábil, outro a cotação do dolar, outro gerando NFs ...

[Júnior Guedes 0]

Hmmm.. entendo.. vou tomar cuidado com isso. De qq forma obrigado pelas dicas.. ajudou muito. Vou marcar o tópico como resolvido.

 

Valeu abraço!!