Solved

Return ID from stored procedure

Posted on 2013-11-07
3
213 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
3 Comments
 
LVL 69

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

912 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now