Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Return ID from stored procedure

Posted on 2013-11-07
3
Medium Priority
?
222 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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

610 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