Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 A double insert due to logic....any ideas?

Author  Topic 

timothymannah
Starting Member

14 Posts
Posted - 2005-06-08 : 01:21:18
My stored procedure inserts the final if twice when the value of @FamilyMembers = 4 i think the if statement logic need a look at....any ideas?


CREATE PROCEDURE [dbo].[spPreTranInsert]
(@PreTransactionID [int]=000000 output,
@Title [varchar](5),
@GivenName [varchar](50),
@LastName [varchar](50),
@DOB [varchar](10),
@Address [varchar](250),
@HomePhone [varchar](50),
@WorkPhone [varchar](50),
@MobilePhone [varchar](50),
@PreferedPhone [varchar](50),
@City [varchar](50),
@State [char](10),
@PostCode [varchar](20),
@Country [varchar](200),
@CardNumber [varchar](50),
@CardType [varchar](15),
@Email [varchar](250),
@ExpiryDate [varchar](10),
@Amount [varchar](20),
@tranDate [varchar](10),
@FamilyMembers [int],
-- 2nd members information
@FirstName2 [varchar](50),
@LastName2 [varchar](50),
@DOB2 [varchar](10),
@ConcessionCard2 [varchar](20),
@Diagnoses2 [varchar](5),
@YearDiag2 [varchar](10),
-- 3rd members information
@FirstName3 [varchar](50),
@LastName3 [varchar](50),
@DOB3 [varchar](10),
@ConcessionCard3 [varchar](20),
@Diagnoses3 [varchar](5),
@YearDiag3 [varchar](10),
-- 4th members dta
@FirstName4 [varchar](50),
@LastName4 [varchar](50),
@DOB4 [varchar](10),
@ConcessionCard4 [varchar](20),
@Diagnoses4 [varchar](5),
@YearDiag4 [varchar](10))


AS

INSERT INTO [dbo].[tbPreTransactions]
([Title],
[GivenName],
[LastName],
[DOB],
[Address],
[HomePhone],
[WorkPhone],
[MobilePhone],
[PreferedPhone],
[City],
[State],
[PostCode],
[Country],
[CardNumber],
[CardType],
[Email],
[expDate],
[TotalFee],
[tranDate],
[FamilyMembers])

VALUES
(@Title,
@GivenName,
@LastName,
@DOB,
@Address,
@HomePhone,
@WorkPhone,
@MobilePhone,
@PreferedPhone,
@City,
@State,
@PostCode,
@Country,
@CardNumber,
@CardType,
@Email,
@ExpiryDate,
@Amount,
@tranDate,
@FamilyMembers)

SET @PreTransactionID = @@IDENTITY

DECLARE @MySystemID int

SELECT @MySystemID = MemFamilyId
FROM [dbo].[tbPreTransactionsFamily]
WHERE PreTransactionID = @PreTransactionID

Declare @FamilyNo2 int
set @FamilyNo2 = 2

Declare @FamilyNo3 int
set @FamilyNo3 = 3

Declare @FamilyNo4 int
set @FamilyNo4 = 4

IF (@FamilyMembers = 4) -- only insert the record if there 3 extra members
BEGIN
INSERT INTO [dbo].[tbPreTransactionsFamily]
([PreTransactionID],[FirstName],[LastName],[DOB],[ConcessionCard],[Diagnoses],[YearDiag],[FamilyNo])

VALUES
(@PreTransactionID,
@FirstName2,
@LastName2,
@DOB2,
@ConcessionCard2,
@Diagnoses2,
@YearDiag2,
@FamilyNo2)

INSERT INTO [dbo].[tbPreTransactionsFamily]
([PreTransactionID],[FirstName],[LastName],[DOB],[ConcessionCard],[Diagnoses],[YearDiag],[FamilyNo])
VALUES
(@PreTransactionID,
@FirstName3,
@LastName3,
@DOB3,
@ConcessionCard3,
@Diagnoses3,
@YearDiag3,
@FamilyNo3)

INSERT INTO [dbo].[tbPreTransactionsFamily]
([PreTransactionID],[FirstName],[LastName],[DOB],[ConcessionCard],[Diagnoses],[YearDiag],[FamilyNo])
VALUES
(@PreTransactionID,
@FirstName4,
@LastName4,
@DOB4,
@ConcessionCard4,
@Diagnoses4,
@YearDiag4,
@FamilyNo4)
END
IF (@FamilyMembers = 3) -- only insert the record if there 2 extra member
BEGIN
INSERT INTO [dbo].[tbPreTransactionsFamily]
([PreTransactionID],[FirstName],[LastName],[DOB],[ConcessionCard],[Diagnoses],[YearDiag],[FamilyNo])

VALUES
(@PreTransactionID,
@FirstName2,
@LastName2,
@DOB2,
@ConcessionCard2,
@Diagnoses2,
@YearDiag2,
@FamilyNo2)

INSERT INTO [dbo].[tbPreTransactionsFamily]
([PreTransactionID],[FirstName],[LastName],[DOB],[ConcessionCard],[Diagnoses],[YearDiag],[FamilyNo])
VALUES
(@PreTransactionID,
@FirstName3,
@LastName3,
@DOB3,
@ConcessionCard3,
@Diagnoses3,
@YearDiag3,
@FamilyNo3)
END
IF (@FamilyMembers = 2) -- only insert the record if there 1 extra member

INSERT INTO [dbo].[tbPreTransactionsFamily]
([PreTransactionID],[FirstName],[LastName],[DOB],[ConcessionCard],[Diagnoses],[YearDiag],[FamilyNo])

VALUES
(@PreTransactionID,
@FirstName2,
@LastName2,
@DOB2,
@ConcessionCard2,
@Diagnoses2,
@YearDiag2,
@FamilyNo2)
GO

Here it is

raclede
Posting Yak Master

180 Posts
Posted - 2005-06-08 : 01:48:32
you may want to use to
so that we can read it properly. your sp is so big, try splitting it into groups.. and make a generic sp for actions that are repeated like for this one:


INSERT INTO [dbo].[tbPreTransactionsFamily] 
([PreTransactionID],[FirstName],[LastName],[DOB],[ConcessionCard],[Diagnoses],[YearDiag],[FamilyNo])

CREATE PROCEDURE Sample
(
@PreTransactionID blahblah
)




then use EXEC Sample @FirstName, @LastName2 .... in the main SP



"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2005-06-08 : 08:15:09
I shortened this a bit, it should work properly:

CREATE PROCEDURE [dbo].[spPreTranInsert]
(@PreTransactionID [int]=000000 output,
@Title [varchar](5),
@GivenName [varchar](50),
@LastName [varchar](50),
@DOB [varchar](10),
@Address [varchar](250),
@HomePhone [varchar](50),
@WorkPhone [varchar](50),
@MobilePhone [varchar](50),
@PreferedPhone [varchar](50),
@City [varchar](50),
@State [char](10),
@PostCode [varchar](20),
@Country [varchar](200),
@CardNumber [varchar](50),
@CardType [varchar](15),
@Email [varchar](250),
@ExpiryDate [varchar](10),
@Amount [varchar](20),
@tranDate [varchar](10),
@FamilyMembers [int],
-- 2nd members information
@FirstName2 [varchar](50),
@LastName2 [varchar](50),
@DOB2 [varchar](10),
@ConcessionCard2 [varchar](20),
@Diagnoses2 [varchar](5),
@YearDiag2 [varchar](10),
-- 3rd members information
@FirstName3 [varchar](50),
@LastName3 [varchar](50),
@DOB3 [varchar](10),
@ConcessionCard3 [varchar](20),
@Diagnoses3 [varchar](5),
@YearDiag3 [varchar](10),
-- 4th members dta
@FirstName4 [varchar](50),
@LastName4 [varchar](50),
@DOB4 [varchar](10),
@ConcessionCard4 [varchar](20),
@Diagnoses4 [varchar](5),
@YearDiag4 [varchar](10))
AS

SET NOCOUNT ON
INSERT INTO [dbo].[tbPreTransactions] ([Title],[GivenName],[LastName],[DOB],[Address],[HomePhone],[WorkPhone],
[MobilePhone],[PreferedPhone],[City],[State], [PostCode],[Country],[CardNumber],[CardType],[Email],
[expDate],[TotalFee],[tranDate],[FamilyMembers])
SELECT @Title,@GivenName,@LastName,@DOB,@Address,@HomePhone,@WorkPhone,@MobilePhone,@PreferedPhone,@City,
@State,@PostCode,@Country,@CardNumber,@CardType,@Email,@ExpiryDate,@Amount,@tranDate,@FamilyMembers

SET @PreTransactionID = @@IDENTITY

INSERT INTO [dbo].[tbPreTransactionsFamily]
([PreTransactionID],[FirstName],[LastName],[DOB],[ConcessionCard],[Diagnoses],[YearDiag],[FamilyNo])
SELECT @PreTransactionID, @FirstName2,@LastName2,@DOB2, @ConcessionCard2,@Diagnoses2,@YearDiag2,@FamilyNo2
WHERE @FamilyMembers>=2

INSERT INTO [dbo].[tbPreTransactionsFamily]
([PreTransactionID],[FirstName],[LastName],[DOB],[ConcessionCard],[Diagnoses],[YearDiag],[FamilyNo])
SELECT @PreTransactionID,@FirstName3,@LastName3,@DOB3,@ConcessionCard3,@Diagnoses3,@YearDiag3,@FamilyNo3
WHERE @FamilyMembers>=3

INSERT INTO [dbo].[tbPreTransactionsFamily]
([PreTransactionID],[FirstName],[LastName],[DOB],[ConcessionCard],[Diagnoses],[YearDiag],[FamilyNo])
SELECT @PreTransactionID,@FirstName4,@LastName4,@DOB4,@ConcessionCard4,@Diagnoses4,@YearDiag4,@FamilyNo4
WHERE @FamilyMembers>=4

I took out the @MySystemID stuff because it was not used anywhere.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site