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:
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'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()
ENDIF 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
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:
|||Sorry. Loose this line:
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' OUTPUTAS
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()
ENDIF 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
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!
