Link to home
Start Free TrialLog in
Avatar of team2005
team2005

asked on

Return ID from stored procedure

Hi!

Need to get last ID after insert, i am using this code:

But it gives me this error message:
Msg 1087, Level 16, State 1, Procedure INSERT_ControlTrans, Line 12
Must declare the table variable "@InsertedId".


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[INSERT_ControlTrans]
@idofcontrol BIGINT, @idofucid BIGINT, @IdOfUser BIGINT
AS

Declare @Activeorg bigint
exec dbo.GET_Active_Userorganisation @UserID=@idofUser,@Organisation=@Activeorg output

DECLARE @InsertedId BIGINT

INSERT INTO dbo.ControlTrans
    (ControlID, UCID, UserID, Organisation, ControlDisplayName, CreatedDate , UpdatedDate, Status) Output Inserted.ControlTransID INTO @InsertedId(ControlTransID)
VALUES
    (@idofcontrol,
	 @idofucid,
	 @IdOfUser, 
	 @Activeorg,
	 (select ControlName+ ',' + GETDATE() from [dbo].[SHOW_UserOrganisationControlLocationObjectQuestion] where ControlID=@idofcontrol and UserID=@IdofUser and  Organisation=@Activeorg),
	 GETDATE(),
	 GETDATE(),
	 0
	 )

GO

Open in new window


What is wrong ?
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

You have declared a variable of type BigInt but your syntax requires a table. What are you trying to do exactly?

You can try:
DECLARE @InsertedId TABLE (
ControlTransID  BIGINT
)
ASKER CERTIFIED SOLUTION
Avatar of dannygonzalez09
dannygonzalez09

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of team2005
team2005

ASKER

thanks