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".
What is wrong ?
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
What is wrong ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
You can try:
DECLARE @InsertedId TABLE (
ControlTransID BIGINT
)