[dba-SQLServer] Stored Procedures - Returning A Value

Paul:

You can use the function SCOPE_IDENTITY( ) to return the last inserted
Identity field from the current procedure. You can also use @@IDENTITY, but
that has some odd concurrency issues, especially with triggers. Look up
either in BOL and you'll get all the gory details.
Hope this helps.

James Barash
 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
Hartland
Sent: Friday, May 27, 2005 6:50 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Stored Procedures - Returning A Value

 
To all,

I'm sure this can be done, but I'm not advanced enough and can't seem to
find it in books online.  I have the following Stored Procedure (SQL
Server 2000):

CREATE PROCEDURE [genesis_insert_UserLog]
	(
	@UserName 		[nvarchar](75),
	@TodayDate	 	[datetime],
	@LogonTime	 	[datetime],
	@CompName		[nvarchar](75),
	@IPAddress		[nvarchar](75)
							)
AS 
SET NOCOUNT ON
INSERT INTO [GenesisBeta].[dbo].[tblUserLog] 
	 ( 	[UserName],
		[Logon_Date],
		[Logon_Time],
		[Computer_Name],
		[IP_Address],
		[IP_Host_Name]
					) 
 
VALUES 
	(
	 	@UserName,
	 	@TodayDate,
	 	@LogonTime,
		@CompName,
		@IPAddress,
		@CompName
					)

SET NOCOUNT OFF
GO

Works fine, but I have a field in tblUserLog called LogonID which is an
autonumber and I would like to return the value to a variable in Visual
Basic 6 after the insert takes place.  

Anyone any ideas ow I would go about this ?

Thanks in advance for any help....


PAUL HARTLAND
Database Designer/Programmer
paul.hartland at isharp.co.uk
ISHARP
DDI - 01922 472031
Mobile - 07730 523179

 

ISHARP (Information Services for Hospitality, Audit, Retail and
Pharmacy) provide IT resources for the Christie Group Stock & Inventory
Services companies.


_______________________________________________

*  This message is confidential.
*  This email, its content and any files transmitted with it are intended
solely for the addressee and may be legally privileged and/or confidential.
*  Access by any other party is unauthorised without the express written
permission of the sender.
*  If you have received this email in error you may not copy or use the
contents, attachments or information in any way and any review, use,
dissemination, forwarding, disclosure, alteration, printing of this
information is strictly prohibited. Please destroy it and notify the sender
via return e-mail.
*  This email has been prepared using information believed by Paul Hartland
to be reliable and accurate, but the company makes no warranty as to
accuracy or completeness. In particular the author does not accept
responsibility for changes made to this email after it was sent.
*  Any opinions expressed in this document are those of the author and do
not necessarily reflect the opinions of the company or its affiliates.

The Orridge web site can be found at:
http://www.orridge.co.uk


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com