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
Solved

Return ID from stored procedure

Posted on 2013-11-07
3
216 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 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Powershell v3 - SQLCMD 3 26
SSMS Opening Mode 9 18
Dynamic SQL select query 4 37
use of sqlCmd command through exec master..xp_cmdshell 7 14
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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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