|
mannahboy
Starting Member
11 Posts |
 Posted - 2005-07-18 : 01:02:09
|
| Below is my sql stored procedure.My problem is within the WHERE clause.This SP is finding records in a group of tables and storing them in another group of tables. The where clause is: WHERE tbPreTransactions.PreTransactionID = tbPreTransactionsExtra.PreTransactionID AND tbPreTransactionsFamily.PreTransactionID = tbPreTransactions.PreTransactionID AND tbPreTransactions.MemberPass = @USER2 AND tbPreTransactions.GivenName = @USER3 AND tbPreTransactions.LastName = @USER4 AND tbPreTransactions.Email = @EMAILSometimes a user doesnt have family members and so the data in that table remains empty. When tbPreTransactionsFamily.PreTransactionID doesnt have a value the SP doesnt work. When there is info it does work.How do I rearrange the WHERE clause to cater for when there no values in the tbPreTransactionsFamily table. If I take out the tbPreTransactionsFamily.PreTransactionID = tbPreTransactions.PreTransactionID line it wont insert the data when it is available. What do i do????CREATE PROCEDURE diabey.spPostTranInsert2 (@systemMemId [int]=000000 output, @EMAIL [varchar](250), @USER1 [varchar](12), -- DOB @USER2 [varchar](50), -- Password @USER3 [varchar](50), -- FirstName @USER4 [varchar](50)) -- LastName AS DECLARE @PreTranId varchar(20) DECLARE @MemberId varchar(50) DECLARE @MemberPass varchar(50) DECLARE @MemExpiry varchar(12) DECLARE @Title varchar(4) DECLARE @GivenName varchar(50) DECLARE @LastName varchar(50) DECLARE @DOB varchar(12) DECLARE @Address varchar(250) DECLARE @City varchar(50) DECLARE @Postcode varchar(50) DECLARE @State char(10) DECLARE @Country varchar(200) DECLARE @EEmail varchar(250) DECLARE @HomePhone varchar(50) DECLARE @WorkPhone varchar(50) DECLARE @MobilePhone varchar(50) DECLARE @PreferedPhone varchar(10) DECLARE @MemCode varchar(3) DECLARE @CardNumber varchar(50) DECLARE @CardType varchar(15) DECLARE @CardExpDate varchar(12) DECLARE @TotalFee varchar(20) DECLARE @Donation varchar(20) DECLARE @tranDate varchar(12) DECLARE @FamilyMembers int DECLARE @Occupation varchar(150) DECLARE @Diagnoses varchar(20) DECLARE @Organisation varchar(150) DECLARE @YearDiag varchar(12) DECLARE @Survey varchar (20) DECLARE @MemType varchar(50) DECLARE @ConcessionCardNo varchar(20) DECLARE @AccessLvl varchar(5) DECLARE @Individual varchar(50) DECLARE @OrgName varchar(50) DECLARE @PrincipleOwner varchar(50) DECLARE @BranchSupport varchar(10) DECLARE @AnnualReturn varchar(10) DECLARE @FamPreTranId varchar(20) DECLARE @FamFirstName2 varchar(50) DECLARE @FamLastName2 varchar(50) DECLARE @FamDOB2 varchar(12) DECLARE @FamConCard2 varchar(30) DECLARE @FamDiag2 varchar(25) DECLARE @FamYearDiag2 varchar(12) DECLARE @FamNo2 varchar(5) DECLARE @FamFirstName3 varchar(50) DECLARE @FamLastName3 varchar(50) DECLARE @FamDOB3 varchar(12) DECLARE @FamConCard3 varchar(30) DECLARE @FamDiag3 varchar(25) DECLARE @FamYearDiag3 varchar(12) DECLARE @FamNo3 varchar(5) DECLARE @FamFirstName4 varchar(50) DECLARE @FamLastName4 varchar(50) DECLARE @FamDOB4 varchar(12) DECLARE @FamConCard4 varchar(30) DECLARE @FamDiag4 varchar(25) DECLARE @FamYearDiag4 varchar(12) DECLARE @FamNo4 varchar(5) SELECT @PreTranId = tbPreTransactions.PreTransactionID, @Title = tbPreTransactions.Title, @GivenName = tbPreTransactions.GivenName, @LastName = tbPreTransactions.LastName, @DOB = tbPreTransactions.DOB, @Address = tbPreTransactions.Address, @City = tbPreTransactions.City, @Postcode = tbPreTransactions.Postcode, @State = tbPreTransactions.state, @Country = tbPreTransactions.Country, @HomePhone = tbPreTransactions.HomePhone, @WorkPhone = tbPreTransactions.WorkPhone, @MobilePhone = tbPreTransactions.MobilePhone, @PreferedPhone = tbPreTransactions.PreferedPhone, @EEmail = tbPreTransactions.Email, @CardNumber = tbPreTransactions.CardNumber, @CardType = tbPreTransactions.CardType, @CardExpDate = tbPreTransactions.expDate, @TotalFee = tbPreTransactions.TotalFee, @Donation = tbPreTransactions.Donation, @tranDate = tbPreTransactions.trandate, @FamilyMembers = tbPreTransactions.FamilyMembers, @MemberPass = tbPreTransactions.MemberPass, @MemExpiry = tbPreTransactions.MembershipExpDate, @MemCode = tbPreTransactions.MembershipCode, @Occupation = tbPreTransactionsExtra.Occupation, @Diagnoses = tbPreTransactionsExtra.Diagnoses, @Organisation = tbPreTransactionsExtra.Organisation, @YearDiag = tbPreTransactionsExtra.YearDiag, @Survey = tbPreTransactionsExtra.Survey, @MemType = tbPreTransactionsExtra.MemType, @ConcessionCardNo = tbPreTransactionsExtra.ConcessionCardNo, @AccessLvl = tbPreTransactionsExtra.AccessLvl, @Individual = tbPreTransactionsExtra.Individual, @OrgName = tbPreTransactionsExtra.OrgName, @PrincipleOwner = tbPreTransactionsExtra.PrincipleOwner, @BranchSupport = tbPreTransactionsExtra.BranchSupport, @AnnualReturn = tbPreTransactionsExtra.AnnualReturn, @FamFirstName2 = tbPreTransactionsFamily.FirstName2, @FamLastName2 = tbPreTransactionsFamily.LastName2, @FamDOB2 = tbPreTransactionsFamily.DOB2, @FamConCard2 = tbPreTransactionsFamily.ConcessionCard2, @FamDiag2 = tbPreTransactionsFamily.Diagnoses2, @FamYearDiag2 = tbPreTransactionsFamily.YearDiag2, @FamNo2 = tbPreTransactionsFamily.FamilyNo2, @FamFirstName3 = tbPreTransactionsFamily.FirstName3, @FamLastName3 = tbPreTransactionsFamily.LastName3, @FamDOB3 = tbPreTransactionsFamily.DOB3, @FamConCard3 = tbPreTransactionsFamily.ConcessionCard3, @FamDiag3 = tbPreTransactionsFamily.Diagnoses3, @FamYearDiag3 = tbPreTransactionsFamily.YearDiag3, @FamNo3 = tbPreTransactionsFamily.FamilyNo3, @FamFirstName4 = tbPreTransactionsFamily.FirstName4, @FamLastName4 = tbPreTransactionsFamily.LastName4, @FamDOB4 = tbPreTransactionsFamily.DOB4, @FamConCard4 = tbPreTransactionsFamily.ConcessionCard4, @FamDiag4 = tbPreTransactionsFamily.Diagnoses4, @FamYearDiag4 = tbPreTransactionsFamily.YearDiag4, @FamNo4 = tbPreTransactionsFamily.FamilyNo4 FROM tbPreTransactions, tbPreTransactionsExtra, tbPreTransactionsFamily WHERE tbPreTransactions.PreTransactionID = tbPreTransactionsExtra.PreTransactionID AND tbPreTransactionsFamily.PreTransactionID = tbPreTransactions.PreTransactionID AND tbPreTransactions.MemberPass = @USER2 AND tbPreTransactions.GivenName = @USER3 AND tbPreTransactions.LastName = @USER4 AND tbPreTransactions.Email = @EMAIL INSERT INTO tbMembers ([MemberPass], [FirstName], [LastName], [DOB], [StrAddress], [suburb], [Postcode], [State], [Country], [MailAddress], [email], [phone], [ExpiryDate], [memberCode]) VALUES (@MemberPass, @GivenName, @LastName, @DOB, @Address, @City, @Postcode, @State, @Country, @Address, @EEmail, @HomePhone, @MemExpiry, @MemCode) SET @systemMemId = @@IDENTITY INSERT INTO tbTransactions ([sysMemID], [Title], [GivenName], [LastName], [DOB], [Address], [City], [State], [Postcode], [Country], [HomePhone], [WorkPhone], [Email], [CardType], [CardNumber], [expDate], [CardHoldName], [TotalFee], [tDate]) VALUES (@systemMemId, @Title, @GivenName, @LastName, @DOB, @Address, @City, @State, @Postcode, @Country, @HomePhone, @WorkPhone, @EEmail, @CardType, @CardNumber, @CardExpDate, @LastName, @TotalFee, @tranDate) INSERT INTO tbMembersExtra ([systemMemID], [Title], [HomePhone], [WorkPhone], [MobilePhone], [PreferedPhone], [Occupation], [Organisation], [Diagnoses], [YearDiag], [Survey], [MemType], [ConcessionCard], [Individual], [OrgName], [PrincipleOwner], [BranchSupport], [AnnualReturn]) VALUES (@systemMemId, @Title, @HomePhone, @WorkPhone, @MobilePhone, @PreferedPhone, @Occupation, @Organisation, @Diagnoses, @YearDiag, @Survey, @MemType, @ConcessionCardNo, @Individual, @OrgName, @PrincipleOwner, @BranchSupport, @AnnualReturn) INSERT INTO tbMemberAccessLvl ([systemMemID], [Accesslvl]) VALUES (@systemMemId, @AccessLvl) Declare @FamilyNo2 int set @FamilyNo2 = 2 Declare @FamilyNo3 int set @FamilyNo3 = 3 Declare @FamilyNo4 int set @FamilyNo4 = 4 IF (@FamilyMembers > 1) -- only insert the record if there 3 extra members INSERT INTO tbMembersFamily ([systemMemId], [FirstName2], [LastName2], [DOB2], [ConcessionCard2], [Diagnoses2], [YearDiag2], [FamilyNo2], [FirstName3], [LastName3], [DOB3], [ConcessionCard3], [Diagnoses3], [YearDiag3], [FamilyNo3], [FirstName4], [LastName4], [DOB4], [ConcessionCard4], [Diagnoses4], [YearDiag4], [FamilyNo4]) VALUES (@systemMemId, @FamFirstName2, @FamLastName2, @FamDOB2, @FamConCard2, @FamDiag2, @FamYearDiag2, @FamNo2, @FamFirstName3, @FamLastName3, @FamDOB3, @FamConCard3, @FamDiag3, @FamYearDiag3, @FamNo3, @FamFirstName4, @FamLastName4, @FamDOB4, @FamConCard4, @FamDiag4, @FamYearDiag4, @FamNo4)GO |
|
A problem in the where clause in my SP