SQL Insert

[Castelo_pt]

Boa tarde eu queria fazer uma Store procedure para criar uma requisição...

com os seguintes campos...

cod_requisicao int Unchecked

data datetime Unchecked

cod_estado int Checked

cod_ofical_requisitante int Checked

cod_tipo_mat_con int Checked

tipo nvarchar(20) Checked

cod_classe int Checked

Nessa SP queria adicionar 1 a 1 tipo o identity.. por exemplo no 1º registo ele metia 1 e depois nas seguintes verificava o ultimo registo k era o 1 e depois metia a 2... e tambem queria fazer com o data por exemplo no ano 2010 podia ter o codigo de requesicao de 1 a 30.. e qd chegar a outro ano começava do 1 outra vez.. e possivel sera mais facil utilizar o scope_identity() ?

Editado Agosto 24, 2010 por Castelo_pt

[fulvio]

Bom dia Castelo,

Vamos ver se entendi. você quer inserir o primeiro registro da tabela e o ID ser 1. Depois inserirá k registros.... Após a inserção de k registros, setaria o ID para 2 e inseriria mais k registros?

Outra coisa: A função "SCOPE_IDENTITY ()" pega o valor do ultimo identity inserido dentro de uma sessao.

[Castelo_pt]

cod_requisicao int Unchecked

data datetime Unchecked

cod_estado int Checked

cod_ofical_requisitante int Checked

cod_tipo_mat_con int Checked

tipo nvarchar(20) Checked

cod_classe int Checked

tenho esse campos todos na tabela de requisicao

as chaves primarias e o cod_requisicao e a data, porque quando fizer uma inserir uma requisicao quero que va acrescentado 1 a 1 no cod_requisicao

faço 2 requisiçoes

cod_requisicao fika 1

e na segunda requisicao fica a 2, faz o que o IDENTITY (1,1) incrementa, o meu problema e quando passar de ano queria que começa-se do 1 por exemplo no ano 2010 havia do 1 ao 50 requisicoes e quando fosse para o ano 2011 começa do 1 o cod de requisicao :P

Cumprimentos. deu para perceber? sou uma beca confuso :P

Obrigado pela a ajuda

[fulvio]

Bom dia Castelo,

No seu exemplo, está querendo separar o cod_requisicao por data, ok? Cada data teria um grupo de codigos distintos. Quando virar o ano, os dados do ano passado serão expurgados? Caso os dados do ano passado fiquem na mesma tabela, o indentity não funcionará (pois não conseguirá duplicá-lo).

Uma implementação que se utiliza as vezes é fazer uma Procedure para controlar isto. A Procedure identifica o ano e incrementa um contador, pegando MAX + 1 de um campo de uma tabela criado apenas para isto. De retorno, a procedure disponibiliza um número. Aí pode utilizá-lo. Quando trocar o ano, a Procedure volta para o valor 1.

OBS.: a procedure não é executada em paralelo. Se tiver mais de uma chamada, os processos serão enfileirados.

Em relação à Chave, não será possível colocar o campo cod_requisicao como PK. O que pode ser feito é criar uma chave composta com os campos cod_requisicao e Data.

Seria esta a dúvida? :.)

[Castelo_pt]

sim e essa a minha duvida... mt obrigado :P mas estou com algumas difilculdade a fazer essa procedure... podia-me adujar-me? desculpa por estar a abusar :) e k estou com algumas difilculdades...

[fulvio]

Bom dia Castelo,

Ok, sem problemas.... rs.

Segue o script abaixo com algumas explicações. Aí você adápta às suas necessidades. Se tiver alguma dúvida, pode postar. :.)

-- Criar tabela
create table dbo.TabelaIdentity (Coluna int, Ano int)

-- Inserir dados
insert into TabelaIdentity values (1, 2010)

-- Criar procedure
CREATE Proc NomeProcedure
  @Retorno Integer OutPut
As
Declare @Identificador Integer
Set NoCount On
Begin Tran

-- Aqui você updata a coluna indetity, pegando pelo ano corrente.
Update TabelaIdentity Set Coluna = (Select Coluna + 1 From TabelaIdentity where datepart(year, getdate())=Ano)
Select @Identificador = Coluna From TabelaIdentity

Commit Tran
Set @Retorno = @Identificador

[Castelo_pt]

Bom dia a minha duvida e a seguinte...

create procedure CriarRequisição

(

@codClasse int,

@tipoRequisicao nvarchar(20),

@codOficialRequisitante int,

@codPreparadoPor int,

@cod_estado int,

@data date,

@out int output

)

as

BEGIN TRANSACTION

declare @codRequisicao int

--Aqui queria fazer uma condição para acrescentar 1 a 1 e quando muda-se de ano recomeçava do 1.

--set @codRequisicao =

--Verifica se no mesmo ano existe um cod_requisição igual.

if not exists(select cod_requisicao from tb_requisicao where cod_requisicao = @codRequisicao and data <> @data)

begin

-- Estado vai sempre a 1 - Pendente / 2 - Entregue

insert into tb_requisicao(cod_requisicao,cod_classe,tipo,cod_preparadoPor,

cod_ofical_requisitante,cod_estado,data)

values(@codRequisicao,@codClasse,@tipoRequisicao,@codPreparadoPor,

@codOficialRequisitante,1,@data);

--Cria a Requisição

set @out = 1

IF @@ERROR <> 0 ROLLBACK TRANSACTION

ELSE COMMIT

END

ELSE

BEGIN

--Existe no mesmo ano o mesmo cod de requisição

set @out = 2

ROLLBACK TRANSACTION

END

era isso que basicamente queria.. mas estou com algumas duvidas cm faze-lo :\

Editado Agosto 30, 2010 por Castelo_pt

[fulvio]

Bom dia Castelo,

Alterei a procedure que te passei e a sua também.

Em específico na sua procedure, na sintaxe "if not exists...", cláusula where:

"...cod_requisicao = @codRequisicao and data <> @data".

Não seria assim?

"...cod_requisicao = @codRequisicao and data = @data".

Coloquei logo abaixo a criação da tabela, a criação da procedure para simular um Identity e a sua procedure alterada. Vê se é isso que precisa. Qualquer dúvida, pode postar ai... :.)

-- Criar tabela
create table dbo.TabelaIdentity (Coluna int, Ano int)

------------------------------------------------------------------------------
-- Criar procedure
CREATE Proc NomeProcedure
  @Retorno Integer OutPut
As
Declare @Identificador Integer
Set NoCount On
Begin Tran

-- Testa se existe o ano já inserido na tabela de Identity
if (select top 1 1 from TabelaIdentity where Ano=datepart(year, getdate())) is null
  insert into TabelaIdentity values (1, datepart(year, getdate()))

-- Aqui você updata a coluna indetity, pegando pelo ano corrente.
Update TabelaIdentity Set Coluna = (Select Coluna + 1 From TabelaIdentity where datepart(year, getdate())=Ano) where datepart(year, getdate())=Ano
Select @Identificador = Coluna From TabelaIdentity where datepart(year, getdate())=Ano

Commit Tran
Set @Retorno = @Identificador

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

create procedure CriarRequisicao
( 

@codClasse int,
@tipoRequisicao nvarchar(20),
@codOficialRequisitante int,
@codPreparadoPor int,
@cod_estado int,
@data date,
@out int output
)
as
BEGIN TRANSACTION
declare @codRequisicao int

-- Aqui você recupera o valor Identity de acordo com o ano corrente. Fiz a verificação do Ano na Procedure.
exec NomeProcedure @Retorno=@codRequisicao output

--Verifica se no mesmo ano existe um cod_requisição igual.
if not exists(select cod_requisicao from tb_requisicao where cod_requisicao = @codRequisicao and data = @data)
begin 
-- Estado vai sempre a 1 - Pendente / 2 - Entregue
insert into tb_requisicao(cod_requisicao,cod_classe,tipo,cod_preparadoPor,
cod_ofical_requisitante,cod_estado,data)
values(@codRequisicao,@codClasse,@tipoRequisicao,@codPreparadoPor,
@codOficialRequisitante,1,@data); 
--Cria a Requisição
set @out = 1
IF @@ERROR <> 0 ROLLBACK TRANSACTION 
ELSE COMMIT
END

ELSE
BEGIN
--Existe no mesmo ano o mesmo cod de requisição
set @out = 2
ROLLBACK TRANSACTION
END

[Castelo_pt]

Muito OBRIGADO. gostava de saber tanto como tu nisso lolol :P

já consegui fazer quando muda de ano começa sempre do um o cod_requisicao fiz assim o que acha :P funciona rapido :P

alter procedure CriarRequisicao
( 
@codClasse int,
@tipoRequisicao int,
@codOficialRequisitante int,
@codPreparadoPor int,
@categoria int,
@data date,
@out int output
)
as
BEGIN TRANSACTION



declare @codRequisicao int, @VerificaCodReq int, @date int 
set @date =DATEPART(year,@data)
set @VerificaCodReq = (select COUNT(cod_requisicao )from tb_requisicao where DATEPART(year,data) =@date)


print @date
--Aqui queria fazer uma condição para acrescentar 1 a 1 e quando muda-se de ano recomeçava do zero.

if (@VerificaCodReq =0)
    BEGIN
    set @codRequisicao = 1
    END
ELSE
    BEGIN
    set @codRequisicao = (select MAX(cod_requisicao )from tb_requisicao where DATEPART(year,data) =@date)+1
    END


--set @codRequisicao = 
--Verifica se a mesma requisição
if not exists(select cod_requisicao from tb_requisicao where cod_requisicao = @codRequisicao and data = @data)
    begin    
    
        -- Estado vai sempre a 1 - Pendente / 2 - Entregue
        insert into tb_requisicao(cod_requisicao,cod_classe,tipoRequisicao,cod_preparadoPor,
        cod_ofical_requisitante,cod_estado,cod_categoria,data)
        values(@codRequisicao,@codClasse,@tipoRequisicao,@codPreparadoPor,
        @codOficialRequisitante,1,@categoria,@data);
                
        --Cria a Requisição
        set @out = 1
        IF @@ERROR <> 0 ROLLBACK TRANSACTION        
        ELSE COMMIT
        END
        
    ELSE
    BEGIN
        --Existe no mesmo ano o mesmo cod de requisição
        set @out = 2
        ROLLBACK TRANSACTION
    END

Editado Setembro 2, 2010 por kuroi
Adicionar tag CODE

[fulvio]

Bom dia Castelo,

Q isso... estou aprendendo ainda... rs.

A implementação está ok!

Apenas uma observação: a função MAX + 1 poderá ser utilizada neste caso pois logo depois do incremento você dá o insert. Se retornasse o valor do incremento para o fonte, a função MAX + 1 não poderia ser utilizada.

Motivo: caso você passasse o codRequisicao para o fonte, haveria um tempo (uma confirmação do usuário, ou processamento do aplicativo, etc) até a efetivação do insert. Neste meio tempo, uma outra chamada à procedure "CriarRequisicao" poderia ser executada, e retornaria o MAX + 1 desatualizado.

Caso não tenha entendido, pode postar que explico com exemplos... :.)