Little help needed with a Stored Proc

[Aptiva Dave]

I am currently writing a stored proc for a database that will help create a new user. The logic I am using is something I had used before, but then I had used three separate stored procs and thought it would be more effecient to just call one stored proc to handle the whole job. Here are the tables I am using: users: userID (primary key, IsIdentity turned on) firstName lastName sex authenticationID (foreign key that points back to the authentication table) contactID (foreign key that points back to the contact table) authentication: authenticationID (primary key, IsIdentity turned on) userName password contact: contactID (primary key, IsIdentity turned on) street city state emailAddress In my stored proc, I pass in all of the parameters needed for each table (listed above, except, of course, the identity fields) and start the inserts. Since I need the contact and authentication inserts done first, I naturally would want to start with those tables, but how would I get the ID fields for each tables so I can insert them into the users table?

[Lost User]

The sproc could return it using a output[^]-parameter. Or you could write a function instead of an sproc, and have that return the identity. Or you check the latest identity values for your transaction :)

I are Troll :suss:

[Corporal Agarn]

Use the @@IDENTITY function within the SP

[Aptiva Dave]

Nevermind. Did a little bit of experimentation and got it to work. Thanks for all the input though.

[Martin Arapovic]

I think this is what you need. Just fill the blanks....

-- =============================================
-- Author:		Martin Arapovic
-- Create date: 19.01.2011
-- Description:	....
-- =============================================
CREATE PROCEDURE InsertUserData 
	(
		-- Add the parameters for the stored procedure here
		-- Define needed parameters
		-- Contact Input Data
		@Street VARCHAR(50), 
		@City VARCHAR(50), 
		@State VARCHAR(50), 
		@EmailAddress VARCHAR(50),
		--Users Input Data
		@UserName VARCHAR(25), 
		@Password VARCHAR(25),
		-- User Input Data
		@FirstName varchar(50),
		@LastName varchar(50),
		@Sex varchar(50)
		-- Add other paramas if you have any
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @ContactID INT 
	DECLARE @AuthenticationID INT 
	
	--
	-- Note: This is only skeleton of the proc that you want to implement
	
	--
	-- Do all in one transaction
	
	BEGIN TRANSACTION
	
	--
    -- Insert Contact data and get ContactID
    INSERT INTO [Contact]
           ([Street]
           ,[City]
           ,[State]
           ,[EmailAddress])
     VALUES
           (@Street
           ,@City
           ,@State
           ,@EmailAddress)
           
    -- GET CointactID 
    SET @ContactID = SCOPE_IDENTITY() 
     
    --
    -- Insert Authentication data and get AuthenticationID
    INSERT INTO [Authentication]
           ([UserName]
           ,[Password])
     VALUES
           (@UserName
           ,@Password)
           
    -- GET Authentication 
    SET @AuthenticationID = SCOPE_IDENTITY() 
     
    -- 
    -- Insert Users data into users table.
    INSERT INTO [Users]
           ([FirstName]
           ,[LastName]
           ,[Sex]
           ,[AuthenticationID]
           ,[ContactID])
     VALUES
           (@FirstName
           ,@LastName
           ,@Sex
           ,@AuthenticationID
           ,@ContactID)

     -- Commit
     COMMIT TRANSACTION
     
     -- This is the general example and you need to add error handling and 
     -- other bussines logic if you have any... :)
           	
END
GO