February 2, 2006 at 11:23 am
hi all
I have a sql procedure that inserts records into a table.
Im trying to get the identity field for the latest record out. But when I do
this:
CREATE PROCEDURE spa_AddEvent
(
@inTitle nvarchar(50),
@inStart_Date nvarchar(50)
)
AS
-- INSERT the new record
INSERT INTO tEvents(Event_Title, Start_Date)
VALUES(@inTitle, @inStart_Date)
GO
Select MAX(event_ID) from tevents
GO
and hit apply, it takes it. But then when I open the procedure up again,
it has removed the second Select statement. Anything I put after the second statement
like Select @recordid as @@identity etc. etc., it removes.
whats going on? how do I get recordid out?
thanks
February 2, 2006 at 11:35 am
The keyword GO ends each batch. In this case the CREATE PROCEDURE batch.
Remove the "GO" after the INSERT.
February 2, 2006 at 12:21 pm
He is correct, but the method your using to return the record inserted is not good.
Is the Event_Id field an identity?
If so you would be more wise to use
select scope_Identity()
as opposed to
Select MAX(event_ID) from tevents.
The problem with this is a this is not wrapped in a transaction, and its possible for more than row to be inserted between the time you insert, and the time the Select MAX(event_ID) from tevents completes.
scope_Identity() will return the identity value of what you just inserted regardless of other transactions.
CREATE PROCEDURE spa_AddEvent
(
@inTitle nvarchar(50),
@inStart_Date nvarchar(50)
)
AS
-- INSERT the new record
INSERT INTO tEvents(Event_Title, Start_Date)
VALUES(@inTitle, @inStart_Date)
Select scope_identity()
GO
February 2, 2006 at 3:30 pm
thanks fellas, that was it.
also, thanks for the help on performance. I knew that wasnt the right way to get it, but I was going to handle that later. Thanks.
MOazzam
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply