March 22, 2004 at 4:13 pm
I am trying to pass the new identity column value from one table to another with the following code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertNewPhone]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertNewPhone]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE InsertNewPhone AS
INSERT INTO phone (PhoneNumber) VALUES ('9999999999')
SELECT @@IDENTITY AS 'newphoneID'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Someone sent me this and I am a real novice at T-SQL. When I post this in a stored procedure, the first 8 lines disappear. This is called from an Access pass-through query. How do I get this working. Thanks for your patience.
SMK
March 22, 2004 at 4:56 pm
You have to DECLARE a Variable and fill that variable with @@IDENTITY....
Here, try this!
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE InsertNewPhone
AS
DECLARE @PhoneID INT
INSERT INTO phone (PhoneNumber) VALUES ('9999999999')
SELECT @PhoneID = (SELECT @@IDENTITY)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
March 22, 2004 at 5:06 pm
Thanks. I think I've got it!
SMK
March 22, 2004 at 5:19 pm
March 23, 2004 at 12:20 am
I prefer to use
SELECT @PhoneID = SCOPE_IDENTITY ()
Refer to BOL for reasons. . . . . .
March 24, 2004 at 3:55 pm
Here is my current SP:
CREATE PROCEDURE dbo.procPhoneInsert
AS
INSERT INTO phone (EmpUpdated) VALUES ('9')
SELECT SCOPE_IDENTITY() AS 'NewPhoneID'
GO
Which works. It returns the NewPhoneID to the FE app (Access 2002).
The line with INSERT INTO just serves the purpose of creating a new row. I don't really need the value "9" in EmpUpdated. I added it because it is a field the user won't see.
Is this optimal syntax or methodology?
Thanks,
SMK
March 24, 2004 at 7:45 pm
In this case you may want to create an OUTPUT parameter to return SCOPE_IDENTITY() thru especially for apps as you can use COmmand Object and return with Execute No Records to avoid having a recordset object build in the app unneccesarily.
CREATE PROCEDURE dbo.procPhoneInsert
@id int OUTPUT
AS
INSERT INTO phone (EmpUpdated) VALUES ('9')
SET @id = SCOPE_IDENTITY()
GO
March 25, 2004 at 4:56 am
Yep, I thought I was looking for an output SP. I made the change but can you help on the FE VBA? Here is the function that calls this SP:
qryInsertNewPhone is a pass-through query that does nothing but execute the stored procedure.
Public Function InsertPhoneRecord() As Long
Dim rsNewPhoneID As New ADODB.Recordset
Dim cmdNew As New ADODB.Command
cmdNew.CommandText = "Select * From qryInsertNewPhone"
cmdNew.CommandType = adCmdText
cmdNew.ActiveConnection = CurrentProject.Connection
Set rsNewPhoneID = cmdNew.Execute()
InsertPhoneRecord = rsNewPhoneID!NewPhoneID
rsNewPhoneID.Close
End Function
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply