HELP w/ Stored Procedure

Can someone explain what I'm doing wrong?

I am passing values to the SPROC and am trying to do the following:

1.) I check to see if the record already exists in the MEMBERS table
NO -> I want to insert a new record and then set @.MEMBER_ID = SCOPE_IDENTITY
YES-> I want to skip the insert and set @.MEMBER_ID to the existing MEMBER_ID

2.) I check to see if the record already exists in the SW_REQUESTS table
NO -> I want to insert a new record (Insert @.MEMBER_ID)
YES -> I want to skip the insert and close out the SPROC

Here is my code that doesn't work. I get an error when setting @.MEMBER_ID = MEMBER_ID:

CREATE PROCEDURE b4b_sw_request
@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0'
AS
SET NOCOUNT ON
IF EXISTS
(SELECT MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SET @.MEMBER_ID = MEMBER_ID
END
ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
VALUES
(@.FName, @.LName, @.Address1, @.Address2, @.City, @.State, @.Zip, @.Email)
SELECT @.MEMBER_ID=SCOPE_IDENTITY()
END
IF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @.MEMBER_ID)
BEGIN
Print ''
END
ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@.MEMBER_ID, @.Send_Updates)
END
GO

You need to declare @.Member_ID as an OUTPUT parameter, if you expect to get it back:

CREATE PROCEDURE b4b_sw_request
@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0' OUTPUT

MEMBERS does have a IDENTITY column, correct?

This section of code:

IF EXISTS
(SELECT MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SET @.MEMBER_ID = MEMBER_ID
END

should be like this:

IF EXISTS
(SELECT * from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SELECT @.Member_ID=MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip
SET @.MEMBER_ID = MEMBER_ID
END

You need to actually select the @.Member_ID value somewhere. Alternately, you could do the select where @.Member_ID=Member_ID outside the IF and then check IsNull(@.Member_ID,0)<>0|||I do have a IDENTITY column in the table MEMBERS. It is MEMBER_ID. When I made the changes you suggested, I still get an error message "Invalid Column Name MEMBER_ID"

Here is the code:

CREATE PROCEDURE b4b_sw_request
@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0' OUTPUT
AS
SET NOCOUNT ON
IF EXISTS
(SELECT * from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SELECT @.Member_ID=MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip
SET @.MEMBER_ID = MEMBER_ID
END
ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
VALUES
(@.FName, @.LName, @.Address1, @.Address2, @.City, @.State, @.Zip, @.Email)
SELECT @.MEMBER_ID=SCOPE_IDENTITY()
END
IF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @.MEMBER_ID)
BEGIN
Print ''
END
ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@.MEMBER_ID, @.Send_Updates)
END
GO

|||Sorry. Loose this line:

SET @.MEMBER_ID = MEMBER_ID

If you still get an error, please specify exactly what line gived the error.|||Thanks Doug. I had just removed it and tested it when I got your message. Works great!