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
