Como recuperar o ID de um registro que eu acabei de gerar por uma procedure?

Question

O que eu preciso é o seguinte. Eu tenho um procedure que faz um insert e eu preciso recuperar por esta procedure o ID gerado. Como fazer isso ? Segue abaixo a minha procedure:

USE [iba_qa]
GO

/****** Object:  StoredProcedure [dbo].[PRC_SPECIAL_LIST_ETAPA1]    Script Date: 06/16/2012 17:17:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		Guilherme Rodrigues Costa
-- Create date: 16/06/2012
-- Description:	Procedure responsável por incluir a etapa 1 do processo de inclusão de special list
-- =============================================
ALTER PROCEDURE [dbo].[PRC_SPECIAL_LIST_ETAPA1] 
    @ID INT = NULL,
	@NOME varchar(50) = '',
    @DESCRICAO varchar(50) = '',
    @URL varchar(100) = ''
AS
BEGIN
	IF @ID IS NULL 
		BEGIN
			-- Insert statements for procedure here
			INSERT INTO [TB_SPECIAL_LISTS]
				   ([NOME]
				   ,[DESCRICAO]
				   ,[URL])
			 VALUES
				   (@NOME
				   ,@DESCRICAO
				   ,@URL)
		END
    ELSE
		BEGIN
			-- Insert statements for procedure here
			UPDATE [TB_SPECIAL_LISTS]
			SET [NOME] = @NOME
			    ,[DESCRICAO] = @DESCRICAO
				,[URL] = @URL
			 WHERE ID = @ID
		END
END


GO

 

---

Guilherme Costa
Email: [e-mail]

Se esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

Answer 1

Neste  seu caso, se você inserir logo depois do insert a seguinte cláusula

set @ID = (SELECT MAX(ID) FROM TB_ESPECIAL_LISTS )

não dá certo? Se precisar retornar o valor talvez seja necessário fazer uma function.

Abraços,

Alexandre.

Answer 2

Guilherme,

Caso seu campo seja IDENTITY, eu faria da seguinte forma:

USE [iba_qa]
GO

/****** Object:  StoredProcedure [dbo].[PRC_SPECIAL_LIST_ETAPA1]    Script Date: 06/16/2012 17:17:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		Guilherme Rodrigues Costa
-- Create date: 16/06/2012
-- Description:	Procedure responsável por incluir a etapa 1 do processo de inclusão de special list
-- =============================================
ALTER PROCEDURE [dbo].[PRC_SPECIAL_LIST_ETAPA1] 
    @ID INT = NULL,
	@NOME varchar(50) = '',
    @DESCRICAO varchar(50) = '',
    @URL varchar(100) = ''
AS
BEGIN
	IF @ID IS NULL 
		BEGIN
			-- Insert statements for procedure here
			INSERT INTO [TB_SPECIAL_LISTS]
				   ([NOME]
				   ,[DESCRICAO]
				   ,[URL])
			 VALUES
				   (@NOME
				   ,@DESCRICAO
				   ,@URL)
		END
    ELSE
		BEGIN
			-- Insert statements for procedure here
			UPDATE [TB_SPECIAL_LISTS]
			SET [NOME] = @NOME
			    ,[DESCRICAO] = @DESCRICAO
				,[URL] = @URL
			 WHERE ID = @ID
		END

SELECT ISNULL(@ID, SCOPE_IDENTITY()) AS 'IdRetorno'

END

GO

Essa linha inserida no fim da PROC, retornará o Id inserido ou que sofreu UPDATE (partindo de que vc sempre informará um Id quando quiser realizar uma atualização nos dados ).

---

[]'s
Philipe Souza
E-mail: [e-mail]

Answer 3

Meu professor de certificação falou para eu fazer utilizando o output ... ficou assim:

USE [iba_qa]
GO

/****** Object:  StoredProcedure [dbo].[PRC_SPECIAL_LIST_ETAPA1]    Script Date: 06/16/2012 17:37:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Guilherme Rodrigues Costa
-- Create date: 16/06/2012
-- Description:	Procedure responsável por incluir a etapa 1 do processo de inclusão de special list
-- =============================================
ALTER PROCEDURE [dbo].[PRC_SPECIAL_LIST_ETAPA1] 
    @ID INT = NULL,
	@NOME varchar(50) = '',
    @DESCRICAO varchar(50) = '',
    @URL varchar(100) = ''
AS
BEGIN
	IF @ID IS NULL 
		BEGIN
			-- Insert statements for procedure here
			INSERT INTO [TB_SPECIAL_LISTS]
				   ([NOME]
				   ,[DESCRICAO]
				   ,[URL])
				   OUTPUT INSERTED.id
			 VALUES
				   (@NOME
				   ,@DESCRICAO
				   ,@URL)
		END
    ELSE
		BEGIN
			-- Insert statements for procedure here
			UPDATE [TB_SPECIAL_LISTS]
			SET [NOME] = @NOME
			    ,[DESCRICAO] = @DESCRICAO
				,[URL] = @URL
			OUTPUT INSERTED.id
			 WHERE ID = @ID
		END
END

GO

Funciou lindo..... acho que esta é a melhor solução.... valeu pela ajuda.

---

Guilherme Costa
Email: [e-mail]

Se esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

Answer 4

Perfeito Guilherme,

marque como resposta para que outras pessoas possam utilizar para agregar conhecimento.

---

Antero Marques

Answer 5

Alexandre,

     Apesar de ser um código válido. Não é recomendado que seja utilizada esta construção pois você poderá ter problemas com os IDs recuperados em caso de concorrencia na sua tabela.

    Imagine a seguinte situação:

     Usuário 1:

      Inicia o Insert e finaliza o Insert  (ID gerado mas ainda nao recuperado = 1)

     Usuário 2 (ao mesmo tempo do usuário 1)

      Inicia o Insert, depois de respeitar o bloqueio, finaliza o Insert Corretamente. (ID gerado mas ainda não recuperado = 2)

     Usuário 1:

     Após milésimos de segundo tenta recuperar o ID com a sua construção:

     o retorno de @ID será? 2 (pois ele é o MAX(ID) da sua construção

     Usuário 2:

      Tenta recuperar o ID com a sua construção:

     o retorno de @ID será 2 também.

     Notou a falha que pode acontecer? Imagine que seja uma venda, você estará vendendo dois produtos para um mesmo cliente (neste caso o cliente 2)... Isso é uma falha que não pode acontecer nunca. Imagine que o cliente 1 deixe de fazer o pagamento.... Internamente você irá pesquisar e verificar que o cliente inadimplente é o cliente 2 ao invés do cliente 1. Agora imagine protestar o cliente 2... A dor de cabeça que vai ser....

    Tente utilizar a sugestão do Guilherme, ou então a sugestão do Philipe para esses casos.

     Att,

  

---

Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008