Problem with @@identity return in stored procedure insert.

I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work.
Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.

CREATE PROCEDURE addMagicRecTest
(
@.theSequence int,
@.theSubject int,
@.theFirstName nvarchar(50)=null
@.theLastName nvarchar(75)=null
)
AS
INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@.theSequence,@.theSubject,@.theFirstName,@.theLastName)
SELECT @.@.identity AS finalNum
magicDataConnect = ConfigurationSettings.AppSettings("myDataConnect")
Response.Write(magicDataConnect)
magicCommand = New SqlDataAdapter("addMagicRecTest", magicDataConnect)
magicCommand.ConnectionType = CommandType.StoredProcedure
magicCommand.SelectCommand.CommandType = CommandType.StoredProcedure
 ' Sequence ID for request
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theSequence", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@.theSequence").Value = "41833"
' Subject for new Wac Ticket
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theSubject", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@.theSubject").Value = "1064"
' First Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theFirstName", SqlDbType.NVarChar, 50))
 magicCommand.SelectCommand.Parameters("@.theFirstName").Value = orderFirstName
' Last Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theLastName", SqlDbType.NVarChar, 75))
magicCommand.SelectCommand.Parameters("@.theLastName").Value = orderLastName
DSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString
End If

I need finalMagicNum2I usually just have the stored proc return the @.@.identity like so:

CREATE PROCEDURE addMagicRecTest
(
@.theSequence int,
@.theSubject int,
@.theFirstName nvarchar(50)=null
@.theLastName nvarchar(75)=null
)
AS
INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@.theSequence,@.theSubject,@.theFirstName,@.theLastName)
Return @.@.Identity

Your stored proc is not returning finalnum and your code has no output param set up in it.

Sam|||Try this code in .NET

DSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables(0).Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables(0).Rows(0).item("finalnum").toString
'OR
'finalMagicNum2 = DSMagic.Tables(0).Rows(0).item(0).toString
End If

Hope this help

Problem with @@IDENTITY in stored procedure

Hello !

I just can't access @.@.IDENTITY in my requests !
Here is my stored procedure : (I removed a few lines for clarity)

set ANSI_NULLSONset QUOTED_IDENTIFIERONGOALTER PROCEDURE [dbo].[AssignerLicence](@.Apprenantint = 0)ASBEGINSET NOCOUNT ON;DECLARE @.NBLICENCESintDECLARE @.COMMANDEIDintDECLARE @.LICENCEIDintSET @.NBLICENCES = (SELECTSUM(commande_licences_reste)FROM CommandeWHERE commande_valide = 1)IF@.NBLICENCES > 0BEGINUPDATE CommandeSET commande_licences_reste = (commande_licences_reste - 1)WHERE commande_id =(SELECT TOP 1 C_min.commande_idFROM CommandeAS C_minWHERE C_min.commande_licences_reste > 5ORDER BY C_min.commande_licences_resteASC,C_min.commande_dateASC);SET @.COMMANDEID =@.@.IDENTITYINSERT INTO Licence (licence_date_debut, commande_id)VALUES (GETDATE(), @.COMMANDEID)SET @.LICENCEID =@.@.IDENTITYUPDATE ApprenantSET licence_id = @.LICENCEIDWHERE apprenant_id = @.ApprenantENDEND

But it always throws an error saying that I can't insert null into commande_id (basically, I think @.@.IDENTITY isn't executed at all)

Any idea why it doen't works ?

Thanks !

@.@.IDENTITY returns the last identity value that the currently executing statement created. Since all you are doing with CommandeID is updating, you can't use @.@.Identity. You need to rework your code similar to the following:

SELECT @.COMMANDEID = (SELECT TOP 1 C_min.commande_idFROM CommandeAS C_minWHERE C_min.commande_licences_reste > 5ORDER BY C_min.commande_licences_resteASC,C_min.commande_dateASC);UPDATE CommandeSET commande_licences_reste = (commande_licences_reste - 1)WHERE commande_id = @.COMMANDEID

Then test if it works.

Problem with @@identity in SQL Server 6.5

In SQL Server 6.5, I have a table called employee
with two fields emp_id and emp_name where emp_id
is an identity column.

The below Stored Procedure is trying to select
the last inserted emp_id using @.@.identity.

CREATE PROCEDURE Employee AS

begin

SET IDENTITY_INSERT employee OFF

SET NOCOUNT ON

declare @.empid int

INSERT INTO employee(emp_name) VALUES
("Soundy")

SET @.empid = @.@.identity

return @.empid

end

But when compiling this procedure, SQL Server displays
an error saying "Incorrect syntax near @.empid".

Can i know where the problem is ?Q1 Can i know where the problem is ?

A1 Try 'Soundy' instead of "Soundy"? For example:

Use TempDB
Go

CREATE TABLE [employee] (
[emp_id] [int] IDENTITY (1, 1) NOT NULL ,
[emp_name] [nvarchar] (50) NULL)
Go

CREATE PROCEDURE ins_Employee
@.pEmpName nvarChar (50) AS
begin
SET IDENTITY_INSERT employee OFF
SET NOCOUNT ON
declare @.empid int
INSERT INTO employee(emp_name) VALUES
(@.pEmpName)
SET @.empid = @.@.identity
return @.empid
end

Go

DECLARE @.RC int
-- exec the Proc
EXEC @.RC = ins_Employee @.pEmpName = 'Soundy'
Select @.RC as '@.RC for ins_employee'
Go

SELECT emp_id, emp_name
FROM employee
Where
emp_id = (SELECT Max(emp_id)FROM employee)

problem with @@IDENTITY

In MSSQL I have auto incrementing PK for the row I'm inserting into. The the
code I use below is to insert into the table. What I need is as soon as I
insert the record, I also need to return back what the PK was of my newly
inserted row. I will need to use this value for another section of the FORM
for use as a FK into another table in which I going insert some other data
based on a few criterias. I tried to us @.@.IDENTITY to no avail. Any help is
greatly appreciated. I pretty new at this stuff and just learning. Thank
You.
You forgot to include your code. Are you using a stored procedure or an
insert statement to insert the data?
Whatever option you are using you need to SELECT @.@.identity immediately
after the statement that performs the insert. If you are running SQL Server
2000 or higher you can use SELECT scope_identity() in place of @.@.identity.
INSERT INTO YourTable (column list) values (values)
SELECT @.@.identity
create proc foo
@.param type.....
as
INSERT INTO YourTable (column list) values (values)
SELECT @.@.identity
go
Keith
"news.microsoftnews" <sapk81@.yahoo.com> wrote in message
news:Oec$paHsEHA.2636@.TK2MSFTNGP09.phx.gbl...
> In MSSQL I have auto incrementing PK for the row I'm inserting into. The
the
> code I use below is to insert into the table. What I need is as soon as I
> insert the record, I also need to return back what the PK was of my newly
> inserted row. I will need to use this value for another section of the
FORM
> for use as a FK into another table in which I going insert some other data
> based on a few criterias. I tried to us @.@.IDENTITY to no avail. Any help
is
> greatly appreciated. I pretty new at this stuff and just learning. Thank
> You.
>
|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OcORPnHsEHA.3876@.TK2MSFTNGP15.phx.gbl...
All relevant stuff.
In addition, if the table you are inserting into has an INSERT trigger which
in turn performs a 'cascaded' INSERT into another table that also has an
identity the value of @.@.IDENTITY or scope_identity() will be that of the
the other table. Beware. THere is a workaround though:
You MUST contrive to cache @.@.IDENTITY coming into your trigger
(i.e. set @.myid = @.@.IDENTITY)and reset it before leaving. If you don't do
this, Access will not be able to correctly track the row inserted and you
will get error
messages (like, the row does not satisfy the underlying criteria, or some
such).
Here is an SQL 2000 idiom to reset @.@.IDENTITY to @.myid (should be done as
the last thing before the trigger exits):
EXECUTE (N'SELECT Identity (Int, ' + Cast(@.myid As Varchar(10)) + ',1) AS id
INTO #Tmp'
G'luck
Malcolm Cook - mec@.stowers-institute.org
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

> You forgot to include your code. Are you using a stored procedure or an
> insert statement to insert the data?
> Whatever option you are using you need to SELECT @.@.identity immediately
> after the statement that performs the insert. If you are running SQL
Server[vbcol=seagreen]
> 2000 or higher you can use SELECT scope_identity() in place of @.@.identity.
>
> INSERT INTO YourTable (column list) values (values)
> SELECT @.@.identity
> create proc foo
> @.param type.....
> as
> INSERT INTO YourTable (column list) values (values)
> SELECT @.@.identity
> go
> --
> Keith
>
> "news.microsoftnews" <sapk81@.yahoo.com> wrote in message
> news:Oec$paHsEHA.2636@.TK2MSFTNGP09.phx.gbl...
> the
I[vbcol=seagreen]
newly[vbcol=seagreen]
> FORM
data
> is
>
sql