Solved

Return ID from stored procedure

Posted on 2013-11-07
3
217 Views
Last Modified: 2013-11-07
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 ?
0
Comment
Question by:team2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39629827
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
)
0
 
LVL 5

Accepted Solution

by:
dannygonzalez09 earned 500 total points
ID: 39630529
You need something like this

----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
----Insert values in real table as well use OUTPUT clause to insert
----values in the temp table.
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (1,'FirstVal')

Open in new window


give this a try
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 TABLE (ControlTransId BigINT)

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

Open in new window

0
 
LVL 2

Author Closing Comment

by:team2005
ID: 39630560
thanks
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question