July 17, 2007 at 1:35 am
Hi,
I have proc that updates a table and due to issues with Access clients and
also a .NET app client I am using a where clause to deal with concurrency in the .NET app.
The problem is using ADO.NET and when I call ExecuteNonQuery on my command object
I am getting back that 2 rows were affected when I should only
get 1 as a I am adding the primary key to the where clause.
I have tried my code on another table and it works fine.
Anyone have any ideas. The proc is below
thanks
Malcolm
<Code>
CREATE PROCEDURE sp_tbrnet_updateEmployees (
@EmployeeID int,
@EmployeeNumber varchar(30),
@FirstName varchar(50),
@LastName varchar(50),
@Title varchar(50),
@EmailName varchar(50),
@Extension varchar(30),
@Address varchar(255),
@City varchar(50),
@StateOrProvince varchar(20),
@PostalCode varchar(20),
@Country varchar(50),
@HomePhone varchar(30),
@WorkPhone varchar(30),
@BillingRate money,
@PayFrequency varchar(50),
@StartDate datetime,
@EndDate datetime,
@FeeRate float,
@FixedFee bit,
@MobilePhone varchar(30),
@Note varchar(255),
@ReferredBy varchar(50),
@TermsOfPaymentDays int,
@PayrollCompany varchar(50),
@PCID int,
@PayType varchar(1),
@TBRToPayrollTransferCode varchar(1),
@PayrollList varchar(50),
@CurrencyAccount varchar(3),
@PayStatus varchar(50),
@PHID int,
@InProcess bit,
@TaxFileNumber varchar(50),
@BSB varchar(50),
@GrossInvoiced float,
@BankAccount varchar(50),
@TaxYearStartDate datetime,
@TaxYearEndDate datetime,
@SuspendPay bit,
@SuspendExpenses bit,
@Active bit,
@ContractOutDate datetime,
@ContractInDate datetime,
@FaxPhone varchar(30),
@DOB datetime,
@DateAdded datetime,
@SFID int,
@Email varchar(100),
@BSB2 varchar(50),
@BankAccount2 varchar(50),
@Visa varchar(50),
@VSID int,
@RecType varchar(1),
@SuperFundAcc varchar(50),
@TempPayFrequency varchar(50),
@WeeksInInvoice int,
@BankDetailsIndicator varchar(1),
@SuspendBeforeDate datetime,
@WorkStateOrProvince varchar(20),
@WPFlag bit,
@SuperFundPlan varchar(50),
@Gender varchar(1),
@ContactAgencyEndUser datetime,
@ExpiryDateLesterSponsored datetime,
@GSTContractStatus int,
@Email2 varchar(50),
@AddressHomeC1 varchar(50),
@AddressHomeC2 varchar(50),
@ArrivalDate datetime,
@DepartureDate datetime,
@AgencyEndUser varchar(50),
@LAFHAAllow money,
@GSTRate money,
@HourlyDailyMthly varchar(50),
@BSB3 varchar(20),
@BankAccount3 varchar(20),
@TransferAmount3 money,
@TransferAmount2 money,
@EmployeeSkills varchar(255),
@EmployeeExperience varchar(255),
@NationalityID int,
@EmployeeStatusID int,
@TaxInfo int,
@SentCV int,
@PartnersNames varchar(50),
@PayrollTax int,
@PendingDate datetime,
@LesterVisaGrantedDate datetime,
@TaxReturnRequired int,
@SkillsProfile varchar(255),
@GeneralNotes varchar(255),
@ActionItems varchar(255),
@SkillsSeparated int,
@ProfileUpdated int,
@LesterListReportHeadings varchar(50),
@NewToList varchar(10),
@DateAvailable datetime,
@Office int,
@TerminationDate datetime,
@DateOnLesterList datetime,
@VisaLength varchar(10),
@OverseasAddress int,
@VisaRenewal varchar(15),
@VisaJobTitle varchar(255),
@EmployeeLocked int,
@SentCVDate datetime,
@TaxInfoDate datetime,
@HolidayPeriod varchar(25),
@PayslipFormat varchar(20),
@ReasonForNotSigningUp int,
@RecruitmentConsultant bit,
@InquiryTypeID int,
@ActiveDate datetime,
@VisaHasBeenSighted bit,
@SetToLesterSponsored bit,
@DimiaMinimum int,
@VisaNominatedDate datetime,
@PreviousSponsorshipDate datetime,
@CancelledDate457 datetime,
@ReasonForLeavingID int,
@EmploymentStartDate datetime,
@Occupation varchar(25),
@AccrueLeaveYesNo int,
@OldVisaID int,
@Original_EmployeeID int,
@Original_EmployeeNumber varchar(30),
@Original_FirstName varchar(50),
@Original_LastName varchar(50),
@Original_Title varchar(50),
@Original_EmailName varchar(50),
@Original_Extension varchar(30),
@Original_Address varchar(255),
@Original_City varchar(50),
@Original_StateOrProvince varchar(20),
@Original_PostalCode varchar(20),
@Original_Country varchar(50),
@Original_HomePhone varchar(30),
@Original_WorkPhone varchar(30),
@Original_BillingRate money,
@Original_PayFrequency varchar(50),
@Original_StartDate datetime,
@Original_EndDate datetime,
@Original_FeeRate float,
@Original_FixedFee bit,
@Original_MobilePhone varchar(30),
@Original_Note varchar(255),
@Original_ReferredBy varchar(50),
@Original_TermsOfPaymentDays int,
@Original_PayrollCompany varchar(50),
@Original_PCID int,
@Original_PayType varchar(1),
@Original_TBRToPayrollTransferCode varchar(1),
@Original_PayrollList varchar(50),
@Original_CurrencyAccount varchar(3),
@Original_PayStatus varchar(50),
@Original_PHID int,
@Original_InProcess bit,
@Original_TaxFileNumber varchar(50),
@Original_BSB varchar(50),
@Original_GrossInvoiced float,
@Original_BankAccount varchar(50),
@Original_TaxYearStartDate datetime,
@Original_TaxYearEndDate datetime,
@Original_SuspendPay bit,
@Original_SuspendExpenses bit,
@Original_Active bit,
@Original_ContractOutDate datetime,
@Original_ContractInDate datetime,
@Original_FaxPhone varchar(30),
@Original_DOB datetime,
@Original_DateAdded datetime,
@Original_SFID int,
@Original_Email varchar(100),
@Original_BSB2 varchar(50),
@Original_BankAccount2 varchar(50),
@Original_Visa varchar(50),
@Original_VSID int,
@Original_RecType varchar(1),
@Original_SuperFundAcc varchar(50),
@Original_TempPayFrequency varchar(50),
@Original_WeeksInInvoice int,
@Original_BankDetailsIndicator varchar(1),
@Original_SuspendBeforeDate datetime,
@Original_WorkStateOrProvince varchar(20),
@Original_WPFlag bit,
@Original_SuperFundPlan varchar(50),
@Original_Gender varchar(1),
@Original_ContactAgencyEndUser datetime,
@Original_ExpiryDateLesterSponsored datetime,
@Original_GSTContractStatus int,
@Original_Email2 varchar(50),
@Original_AddressHomeC1 varchar(50),
@Original_AddressHomeC2 varchar(50),
@Original_ArrivalDate datetime,
@Original_DepartureDate datetime,
@Original_AgencyEndUser varchar(50),
@Original_LAFHAAllow money,
@Original_GSTRate money,
@Original_HourlyDailyMthly varchar(50),
@Original_BSB3 varchar(20),
@Original_BankAccount3 varchar(20),
@Original_TransferAmount3 money,
@Original_TransferAmount2 money,
@Original_EmployeeSkills varchar(255),
@Original_EmployeeExperience varchar(255),
@Original_NationalityID int,
@Original_EmployeeStatusID int,
@Original_TaxInfo int,
@Original_SentCV int,
@Original_PartnersNames varchar(50),
@Original_PayrollTax int,
@Original_PendingDate datetime,
@Original_LesterVisaGrantedDate datetime,
@Original_TaxReturnRequired int,
@Original_SkillsProfile varchar(255),
@Original_GeneralNotes varchar(255),
@Original_ActionItems varchar(255),
@Original_SkillsSeparated int,
@Original_ProfileUpdated int,
@Original_LesterListReportHeadings varchar(50),
@Original_NewToList varchar(10),
@Original_DateAvailable datetime,
@Original_Office int,
@Original_TerminationDate datetime,
@Original_DateOnLesterList datetime,
@Original_VisaLength varchar(10),
@Original_OverseasAddress int,
@Original_VisaRenewal varchar(15),
@Original_VisaJobTitle varchar(255),
@Original_EmployeeLocked int,
@Original_SentCVDate datetime,
@Original_TaxInfoDate datetime,
@Original_HolidayPeriod varchar(25),
@Original_PayslipFormat varchar(20),
@Original_ReasonForNotSigningUp int,
@Original_RecruitmentConsultant bit,
@Original_InquiryTypeID int,
@Original_ActiveDate datetime,
@Original_VisaHasBeenSighted bit,
@Original_SetToLesterSponsored bit,
@Original_DimiaMinimum int,
@Original_VisaNominatedDate datetime,
@Original_PreviousSponsorshipDate datetime,
@Original_CancelledDate457 datetime,
@Original_ReasonForLeavingID int,
@Original_EmploymentStartDate datetime,
@Original_Occupation varchar(25),
@Original_AccrueLeaveYesNo int,
@Original_OldVisaID int
)
AS
UPDATE Employees
SET
EmployeeNumber = @EmployeeNumber,
FirstName = @FirstName,
LastName = @LastName,
Title = @Title,
EmailName = @EmailName,
Extension = @Extension,
Address = @Address,
City = @City,
StateOrProvince = @StateOrProvince,
PostalCode = @PostalCode,
Country = @Country,
HomePhone = @HomePhone,
WorkPhone = @WorkPhone,
BillingRate = @BillingRate,
PayFrequency = @PayFrequency,
StartDate = @StartDate,
EndDate = @EndDate,
FeeRate = @FeeRate,
FixedFee = @FixedFee,
MobilePhone = @MobilePhone,
Note = @Note,
ReferredBy = @ReferredBy,
TermsOfPaymentDays = @TermsOfPaymentDays,
PayrollCompany = @PayrollCompany,
PCID = @PCID,
PayType = @PayType,
TBRToPayrollTransferCode = @TBRToPayrollTransferCode,
PayrollList = @PayrollList,
CurrencyAccount = @CurrencyAccount,
PayStatus = @PayStatus,
PHID = @PHID,
InProcess = @InProcess,
TaxFileNumber = @TaxFileNumber,
BSB = @bsb,
GrossInvoiced = @GrossInvoiced,
BankAccount = @BankAccount,
TaxYearStartDate = @TaxYearStartDate,
TaxYearEndDate = @TaxYearEndDate,
SuspendPay = @SuspendPay,
SuspendExpenses = @SuspendExpenses,
Active = @active,
ContractOutDate = @ContractOutDate,
ContractInDate = @ContractInDate,
FaxPhone = @FaxPhone,
DOB = @DOB,
DateAdded = @DateAdded,
SFID = @SFID,
Email = @Email,
BSB2 = @BSB2,
BankAccount2 = @BankAccount2,
Visa = @visa,
VSID = @VSID,
RecType = @RecType,
SuperFundAcc = @SuperFundAcc,
TempPayFrequency = @TempPayFrequency,
WeeksInInvoice = @WeeksInInvoice,
BankDetailsIndicator = @BankDetailsIndicator,
SuspendBeforeDate = @SuspendBeforeDate,
WorkStateOrProvince = @WorkStateOrProvince,
WPFlag = @WPFlag,
SuperFundPlan = @SuperFundPlan,
Gender = @Gender,
ContactAgencyEndUser = @ContactAgencyEndUser,
ExpiryDateLesterSponsored = @ExpiryDateLesterSponsored,
GSTContractStatus = @GSTContractStatus,
Email2 = @Email2,
AddressHomeC1 = @AddressHomeC1,
AddressHomeC2 = @AddressHomeC2,
ArrivalDate = @ArrivalDate,
DepartureDate = @DepartureDate,
AgencyEndUser = @AgencyEndUser,
LAFHAAllow = @LAFHAAllow,
GSTRate = @GSTRate,
HourlyDailyMthly = @HourlyDailyMthly,
BSB3 = @BSB3,
BankAccount3 = @BankAccount3,
TransferAmount3 = @TransferAmount3,
TransferAmount2 = @TransferAmount2,
EmployeeSkills = @EmployeeSkills,
EmployeeExperience = @EmployeeExperience,
NationalityID = @NationalityID,
EmployeeStatusID = @EmployeeStatusID,
TaxInfo = @TaxInfo,
SentCV = @SentCV,
PartnersNames = @PartnersNames,
PayrollTax = @PayrollTax,
PendingDate = @PendingDate,
LesterVisaGrantedDate = @LesterVisaGrantedDate,
TaxReturnRequired = @TaxReturnRequired,
SkillsProfile = @SkillsProfile,
GeneralNotes = @GeneralNotes,
ActionItems = @ActionItems,
SkillsSeparated = @SkillsSeparated,
ProfileUpdated = @ProfileUpdated,
LesterListReportHeadings = @LesterListReportHeadings,
NewToList = @NewToList,
DateAvailable = @DateAvailable,
Office = @office,
TerminationDate = @TerminationDate,
DateOnLesterList = @DateOnLesterList,
VisaLength = @VisaLength,
OverseasAddress = @OverseasAddress,
VisaRenewal = @VisaRenewal,
VisaJobTitle = @VisaJobTitle,
EmployeeLocked = @EmployeeLocked,
SentCVDate = @SentCVDate,
TaxInfoDate = @TaxInfoDate,
HolidayPeriod = @HolidayPeriod,
PayslipFormat = @PayslipFormat,
ReasonForNotSigningUp = @ReasonForNotSigningUp,
RecruitmentConsultant = @RecruitmentConsultant,
InquiryTypeID = @InquiryTypeID,
ActiveDate = @ActiveDate,
VisaHasBeenSighted = @VisaHasBeenSighted,
SetToLesterSponsored = @SetToLesterSponsored,
DimiaMinimum = @DimiaMinimum,
VisaNominatedDate = @VisaNominatedDate,
PreviousSponsorshipDate = @PreviousSponsorshipDate,
CancelledDate457 = @CancelledDate457,
ReasonForLeavingID = @ReasonForLeavingID,
EmploymentStartDate = @EmploymentStartDate,
Occupation = @Occupation,
AccrueLeaveYesNo = @AccrueLeaveYesNo,
OldVisaID = @OldVisaID
WHERE (EmployeeID = @EmployeeID) AND
(EmployeeNumber = @Original_EmployeeNumber OR @Original_EmployeeNumber IS NULL AND EmployeeNumber IS NULL) AND
(FirstName = @Original_FirstName OR @Original_FirstName IS NULL AND FirstName IS NULL) AND
(LastName = @Original_LastName OR @Original_LastName IS NULL AND LastName IS NULL) AND
(Title = @Original_Title OR @Original_Title IS NULL AND Title IS NULL) AND
(EmailName = @Original_EmailName OR @Original_EmailName IS NULL AND EmailName IS NULL) AND
(Extension = @Original_Extension OR @Original_Extension IS NULL AND Extension IS NULL) AND
(Address = @Original_Address OR @Original_Address IS NULL AND Address IS NULL) AND
(City = @Original_City OR @Original_City IS NULL AND City IS NULL) AND
(StateOrProvince = @Original_StateOrProvince OR @Original_StateOrProvince IS NULL AND StateOrProvince IS NULL) AND
(PostalCode = @Original_PostalCode OR @Original_PostalCode IS NULL AND PostalCode IS NULL) AND
(Country = @Original_Country OR @Original_Country IS NULL AND Country IS NULL) AND
(HomePhone = @Original_HomePhone OR @Original_HomePhone IS NULL AND HomePhone IS NULL) AND
(WorkPhone = @Original_WorkPhone OR @Original_WorkPhone IS NULL AND WorkPhone IS NULL) AND
(BillingRate = @Original_BillingRate OR @Original_BillingRate IS NULL AND BillingRate IS NULL) AND
(PayFrequency = @Original_PayFrequency OR @Original_PayFrequency IS NULL AND PayFrequency IS NULL) AND
(StartDate = @Original_StartDate OR @Original_StartDate IS NULL AND StartDate IS NULL) AND
(EndDate = @Original_EndDate OR @Original_EndDate IS NULL AND EndDate IS NULL) AND
(FeeRate = @Original_FeeRate OR @Original_FeeRate IS NULL AND FeeRate IS NULL) AND
(FixedFee = @Original_FixedFee OR @Original_FixedFee IS NULL AND FixedFee IS NULL) AND
(MobilePhone = @Original_MobilePhone OR @Original_MobilePhone IS NULL AND MobilePhone IS NULL) AND
(Note = @Original_Note OR @Original_Note IS NULL AND Note IS NULL) AND
(ReferredBy = @Original_ReferredBy OR @Original_ReferredBy IS NULL AND ReferredBy IS NULL) AND
(TermsOfPaymentDays = @Original_TermsOfPaymentDays OR @Original_TermsOfPaymentDays IS NULL AND TermsOfPaymentDays IS NULL) AND
(PayrollCompany = @Original_PayrollCompany OR @Original_PayrollCompany IS NULL AND PayrollCompany IS NULL) AND
(PCID = @Original_PCID OR @Original_PCID IS NULL AND PCID IS NULL) AND
(PayType = @Original_PayType OR @Original_PayType IS NULL AND PayType IS NULL) AND
(TBRToPayrollTransferCode = @Original_TBRToPayrollTransferCode OR @Original_TBRToPayrollTransferCode IS NULL AND TBRToPayrollTransferCode IS NULL) AND
(PayrollList = @Original_PayrollList OR @Original_PayrollList IS NULL AND PayrollList IS NULL) AND
(CurrencyAccount = @Original_CurrencyAccount OR @Original_CurrencyAccount IS NULL AND CurrencyAccount IS NULL) AND
(PayStatus = @Original_PayStatus OR @Original_PayStatus IS NULL AND PayStatus IS NULL) AND
(PHID = @Original_PHID OR @Original_PHID IS NULL AND PHID IS NULL) AND
(InProcess = @Original_InProcess OR @Original_InProcess IS NULL AND InProcess IS NULL) AND
(TaxFileNumber = @Original_TaxFileNumber OR @Original_TaxFileNumber IS NULL AND TaxFileNumber IS NULL) AND
(BSB = @Original_BSB OR @Original_BSB IS NULL AND BSB IS NULL) AND
(GrossInvoiced = @Original_GrossInvoiced OR @Original_GrossInvoiced IS NULL AND GrossInvoiced IS NULL) AND
(BankAccount = @Original_BankAccount OR @Original_BankAccount IS NULL AND BankAccount IS NULL) AND
(TaxYearStartDate = @Original_TaxYearStartDate OR @Original_TaxYearStartDate IS NULL AND TaxYearStartDate IS NULL) AND
(TaxYearEndDate = @Original_TaxYearEndDate OR @Original_TaxYearEndDate IS NULL AND TaxYearEndDate IS NULL) AND
(SuspendPay = @Original_SuspendPay OR @Original_SuspendPay IS NULL AND SuspendPay IS NULL) AND
(SuspendExpenses = @Original_SuspendExpenses OR @Original_SuspendExpenses IS NULL AND SuspendExpenses IS NULL) AND
(Active = @Original_Active OR @Original_Active IS NULL AND Active IS NULL) AND
(ContractOutDate = @Original_ContractOutDate OR @Original_ContractOutDate IS NULL AND ContractOutDate IS NULL) AND
(ContractInDate = @Original_ContractInDate OR @Original_ContractInDate IS NULL AND ContractInDate IS NULL) AND
(FaxPhone = @Original_FaxPhone OR @Original_FaxPhone IS NULL AND FaxPhone IS NULL) AND
(DOB = @Original_DOB OR @Original_DOB IS NULL AND DOB IS NULL) AND
(DateAdded = @Original_DateAdded OR @Original_DateAdded IS NULL AND DateAdded IS NULL) AND
(SFID = @Original_SFID OR @Original_SFID IS NULL AND SFID IS NULL) AND
(Email = @Original_Email OR @Original_Email IS NULL AND Email IS NULL) AND
(BSB2 = @Original_BSB2 OR @Original_BSB2 IS NULL AND BSB2 IS NULL) AND
(BankAccount2 = @Original_BankAccount2 OR @Original_BankAccount2 IS NULL AND BankAccount2 IS NULL) AND
(Visa = @Original_Visa OR @Original_Visa IS NULL AND Visa IS NULL) AND
(VSID = @Original_VSID OR @Original_VSID IS NULL AND VSID IS NULL) AND
(RecType = @Original_RecType OR @Original_RecType IS NULL AND RecType IS NULL) AND
(SuperFundAcc = @Original_SuperFundAcc OR @Original_SuperFundAcc IS NULL AND SuperFundAcc IS NULL) AND
(TempPayFrequency = @Original_TempPayFrequency OR @Original_TempPayFrequency IS NULL AND TempPayFrequency IS NULL) AND
(WeeksInInvoice = @Original_WeeksInInvoice OR @Original_WeeksInInvoice IS NULL AND WeeksInInvoice IS NULL) AND
(BankDetailsIndicator = @Original_BankDetailsIndicator OR @Original_BankDetailsIndicator IS NULL AND BankDetailsIndicator IS NULL) AND
(SuspendBeforeDate = @Original_SuspendBeforeDate OR @Original_SuspendBeforeDate IS NULL AND SuspendBeforeDate IS NULL) AND
(WorkStateOrProvince = @Original_WorkStateOrProvince OR @Original_WorkStateOrProvince IS NULL AND WorkStateOrProvince IS NULL) AND
(WPFlag = @Original_WPFlag OR @Original_WPFlag IS NULL AND WPFlag IS NULL) AND
(SuperFundPlan = @Original_SuperFundPlan OR @Original_SuperFundPlan IS NULL AND SuperFundPlan IS NULL) AND
(Gender = @Original_Gender OR @Original_Gender IS NULL AND Gender IS NULL) AND
(ContactAgencyEndUser = @Original_ContactAgencyEndUser OR @Original_ContactAgencyEndUser IS NULL AND ContactAgencyEndUser IS NULL) AND
(ExpiryDateLesterSponsored = @Original_ExpiryDateLesterSponsored OR @Original_ExpiryDateLesterSponsored IS NULL AND ExpiryDateLesterSponsored IS NULL) AND
(GSTContractStatus = @Original_GSTContractStatus OR @Original_GSTContractStatus IS NULL AND GSTContractStatus IS NULL) AND
(Email2 = @Original_Email2 OR @Original_Email2 IS NULL AND Email2 IS NULL) AND
(AddressHomeC1 = @Original_AddressHomeC1 OR @Original_AddressHomeC1 IS NULL AND AddressHomeC1 IS NULL) AND
(AddressHomeC2 = @Original_AddressHomeC2 OR @Original_AddressHomeC2 IS NULL AND AddressHomeC2 IS NULL) AND
(ArrivalDate = @Original_ArrivalDate OR @Original_ArrivalDate IS NULL AND ArrivalDate IS NULL) AND
(DepartureDate = @Original_DepartureDate OR @Original_DepartureDate IS NULL AND DepartureDate IS NULL) AND
(AgencyEndUser = @Original_AgencyEndUser OR @Original_AgencyEndUser IS NULL AND AgencyEndUser IS NULL) AND
(LAFHAAllow = @Original_LAFHAAllow OR @Original_LAFHAAllow IS NULL AND LAFHAAllow IS NULL) AND
(GSTRate = @Original_GSTRate OR @Original_GSTRate IS NULL AND GSTRate IS NULL) AND
(HourlyDailyMthly = @Original_HourlyDailyMthly OR @Original_HourlyDailyMthly IS NULL AND HourlyDailyMthly IS NULL) AND
(BSB3 = @Original_BSB3 OR @Original_BSB3 IS NULL AND BSB3 IS NULL) AND
(BankAccount3 = @Original_BankAccount3 OR @Original_BankAccount3 IS NULL AND BankAccount3 IS NULL) AND
(TransferAmount3 = @Original_TransferAmount3 OR @Original_TransferAmount3 IS NULL AND TransferAmount3 IS NULL) AND
(TransferAmount2 = @Original_TransferAmount2 OR @Original_TransferAmount2 IS NULL AND TransferAmount2 IS NULL) AND
(EmployeeSkills = @Original_EmployeeSkills OR @Original_EmployeeSkills IS NULL AND EmployeeSkills IS NULL) AND
(EmployeeExperience = @Original_EmployeeExperience OR @Original_EmployeeExperience IS NULL AND EmployeeExperience IS NULL) AND
(NationalityID = @Original_NationalityID OR @Original_NationalityID IS NULL AND NationalityID IS NULL) AND
(EmployeeStatusID = @Original_EmployeeStatusID OR @Original_EmployeeStatusID IS NULL AND EmployeeStatusID IS NULL) AND
(TaxInfo = @Original_TaxInfo OR @Original_TaxInfo IS NULL AND TaxInfo IS NULL) AND
(SentCV = @Original_SentCV OR @Original_SentCV IS NULL AND SentCV IS NULL) AND
(PartnersNames = @Original_PartnersNames OR @Original_PartnersNames IS NULL AND PartnersNames IS NULL) AND
(PayrollTax = @Original_PayrollTax OR @Original_PayrollTax IS NULL AND PayrollTax IS NULL) AND
(PendingDate = @Original_PendingDate OR @Original_PendingDate IS NULL AND PendingDate IS NULL) AND
(LesterVisaGrantedDate = @Original_LesterVisaGrantedDate OR @Original_LesterVisaGrantedDate IS NULL AND LesterVisaGrantedDate IS NULL) AND
(TaxReturnRequired = @Original_TaxReturnRequired OR @Original_TaxReturnRequired IS NULL AND TaxReturnRequired IS NULL) AND
(SkillsProfile = @Original_SkillsProfile OR @Original_SkillsProfile IS NULL AND SkillsProfile IS NULL) AND
(GeneralNotes = @Original_GeneralNotes OR @Original_GeneralNotes IS NULL AND GeneralNotes IS NULL) AND
(ActionItems = @Original_ActionItems OR @Original_ActionItems IS NULL AND ActionItems IS NULL) AND
(SkillsSeparated = @Original_SkillsSeparated OR @Original_SkillsSeparated IS NULL AND SkillsSeparated IS NULL) AND
(ProfileUpdated = @Original_ProfileUpdated OR @Original_ProfileUpdated IS NULL AND ProfileUpdated IS NULL) AND
(LesterListReportHeadings = @Original_LesterListReportHeadings OR @Original_LesterListReportHeadings IS NULL AND LesterListReportHeadings IS NULL) AND
(NewToList = @Original_NewToList OR @Original_NewToList IS NULL AND NewToList IS NULL) AND
(DateAvailable = @Original_DateAvailable OR @Original_DateAvailable IS NULL AND DateAvailable IS NULL) AND
(Office = @Original_Office OR @Original_Office IS NULL AND Office IS NULL) AND
(TerminationDate = @Original_TerminationDate OR @Original_TerminationDate IS NULL AND TerminationDate IS NULL) AND
(DateOnLesterList = @Original_DateOnLesterList OR @Original_DateOnLesterList IS NULL AND DateOnLesterList IS NULL) AND
(VisaLength = @Original_VisaLength OR @Original_VisaLength IS NULL AND VisaLength IS NULL) AND
(OverseasAddress = @Original_OverseasAddress OR @Original_OverseasAddress IS NULL AND OverseasAddress IS NULL) AND
(VisaRenewal = @Original_VisaRenewal OR @Original_VisaRenewal IS NULL AND VisaRenewal IS NULL) AND
(VisaJobTitle = @Original_VisaJobTitle OR @Original_VisaJobTitle IS NULL AND VisaJobTitle IS NULL) AND
(EmployeeLocked = @Original_EmployeeLocked OR @Original_EmployeeLocked IS NULL AND EmployeeLocked IS NULL) AND
(SentCVDate = @Original_SentCVDate OR @Original_SentCVDate IS NULL AND SentCVDate IS NULL) AND
(TaxInfoDate = @Original_TaxInfoDate OR @Original_TaxInfoDate IS NULL AND TaxInfoDate IS NULL) AND
(HolidayPeriod = @Original_HolidayPeriod OR @Original_HolidayPeriod IS NULL AND HolidayPeriod IS NULL) AND
(PayslipFormat = @Original_PayslipFormat OR @Original_PayslipFormat IS NULL AND PayslipFormat IS NULL) AND
(ReasonForNotSigningUp = @Original_ReasonForNotSigningUp OR @Original_ReasonForNotSigningUp IS NULL AND ReasonForNotSigningUp IS NULL) AND
(RecruitmentConsultant = @Original_RecruitmentConsultant OR @Original_RecruitmentConsultant IS NULL AND RecruitmentConsultant IS NULL) AND
(InquiryTypeID = @Original_InquiryTypeID OR @Original_InquiryTypeID IS NULL AND InquiryTypeID IS NULL) AND
(ActiveDate = @Original_ActiveDate OR @Original_ActiveDate IS NULL AND ActiveDate IS NULL) AND
(VisaHasBeenSighted = @Original_VisaHasBeenSighted OR @Original_VisaHasBeenSighted IS NULL AND VisaHasBeenSighted IS NULL) AND
(SetToLesterSponsored = @Original_SetToLesterSponsored OR @Original_SetToLesterSponsored IS NULL AND SetToLesterSponsored IS NULL) AND
(DimiaMinimum = @Original_DimiaMinimum OR @Original_DimiaMinimum IS NULL AND DimiaMinimum IS NULL) AND
(VisaNominatedDate = @Original_VisaNominatedDate OR @Original_VisaNominatedDate IS NULL AND VisaNominatedDate IS NULL) AND
(PreviousSponsorshipDate = @Original_PreviousSponsorshipDate OR @Original_PreviousSponsorshipDate IS NULL AND PreviousSponsorshipDate IS NULL) AND
(CancelledDate457 = @Original_CancelledDate457 OR @Original_CancelledDate457 IS NULL AND CancelledDate457 IS NULL) AND
(ReasonForLeavingID = @Original_ReasonForLeavingID OR @Original_ReasonForLeavingID IS NULL AND ReasonForLeavingID IS NULL) AND
(EmploymentStartDate = @Original_EmploymentStartDate OR @Original_EmploymentStartDate IS NULL AND EmploymentStartDate IS NULL) AND
(Occupation = @Original_Occupation OR @Original_Occupation IS NULL AND Occupation IS NULL) AND
(AccrueLeaveYesNo = @Original_AccrueLeaveYesNo OR @Original_AccrueLeaveYesNo IS NULL AND AccrueLeaveYesNo IS NULL) AND
(OldVisaID = @Original_OldVisaID OR @Original_OldVisaID IS NULL AND OldVisaID IS NULL)
RETURN
</Code>
July 17, 2007 at 1:55 am
IF EmployeeID is primary key then use only primary key in where
UPDATE Employees
SET
EmployeeNumber = @EmployeeNumber,
FirstName = @FirstName,
LastName = @LastName,
Title = @Title,
EmailName = @EmailName,
Extension = @Extension,
Address = @Address,
City = @City,
StateOrProvince = @StateOrProvince,
PostalCode = @PostalCode,
Country = @Country,
HomePhone = @HomePhone,
WorkPhone = @WorkPhone,
BillingRate = @BillingRate,
PayFrequency = @PayFrequency,
StartDate = @StartDate,
EndDate = @EndDate,
FeeRate = @FeeRate,
FixedFee = @FixedFee,
MobilePhone = @MobilePhone,
Note = @Note,
ReferredBy = @ReferredBy,
TermsOfPaymentDays = @TermsOfPaymentDays,
PayrollCompany = @PayrollCompany,
PCID = @PCID,
PayType = @PayType,
TBRToPayrollTransferCode = @TBRToPayrollTransferCode,
PayrollList = @PayrollList,
CurrencyAccount = @CurrencyAccount,
PayStatus = @PayStatus,
PHID = @PHID,
InProcess = @InProcess,
TaxFileNumber = @TaxFileNumber,
BSB = @bsb,
GrossInvoiced = @GrossInvoiced,
BankAccount = @BankAccount,
TaxYearStartDate = @TaxYearStartDate,
TaxYearEndDate = @TaxYearEndDate,
SuspendPay = @SuspendPay,
SuspendExpenses = @SuspendExpenses,
Active = @active,
ContractOutDate = @ContractOutDate,
ContractInDate = @ContractInDate,
FaxPhone = @FaxPhone,
DOB = @DOB,
DateAdded = @DateAdded,
SFID = @SFID,
Email = @Email,
BSB2 = @BSB2,
BankAccount2 = @BankAccount2,
Visa = @visa,
VSID = @VSID,
RecType = @RecType,
SuperFundAcc = @SuperFundAcc,
TempPayFrequency = @TempPayFrequency,
WeeksInInvoice = @WeeksInInvoice,
BankDetailsIndicator = @BankDetailsIndicator,
SuspendBeforeDate = @SuspendBeforeDate,
WorkStateOrProvince = @WorkStateOrProvince,
WPFlag = @WPFlag,
SuperFundPlan = @SuperFundPlan,
Gender = @Gender,
ContactAgencyEndUser = @ContactAgencyEndUser,
ExpiryDateLesterSponsored = @ExpiryDateLesterSponsored,
GSTContractStatus = @GSTContractStatus,
Email2 = @Email2,
AddressHomeC1 = @AddressHomeC1,
AddressHomeC2 = @AddressHomeC2,
ArrivalDate = @ArrivalDate,
DepartureDate = @DepartureDate,
AgencyEndUser = @AgencyEndUser,
LAFHAAllow = @LAFHAAllow,
GSTRate = @GSTRate,
HourlyDailyMthly = @HourlyDailyMthly,
BSB3 = @BSB3,
BankAccount3 = @BankAccount3,
TransferAmount3 = @TransferAmount3,
TransferAmount2 = @TransferAmount2,
EmployeeSkills = @EmployeeSkills,
EmployeeExperience = @EmployeeExperience,
NationalityID = @NationalityID,
EmployeeStatusID = @EmployeeStatusID,
TaxInfo = @TaxInfo,
SentCV = @SentCV,
PartnersNames = @PartnersNames,
PayrollTax = @PayrollTax,
PendingDate = @PendingDate,
LesterVisaGrantedDate = @LesterVisaGrantedDate,
TaxReturnRequired = @TaxReturnRequired,
SkillsProfile = @SkillsProfile,
GeneralNotes = @GeneralNotes,
ActionItems = @ActionItems,
SkillsSeparated = @SkillsSeparated,
ProfileUpdated = @ProfileUpdated,
LesterListReportHeadings = @LesterListReportHeadings,
NewToList = @NewToList,
DateAvailable = @DateAvailable,
Office = @office,
TerminationDate = @TerminationDate,
DateOnLesterList = @DateOnLesterList,
VisaLength = @VisaLength,
OverseasAddress = @OverseasAddress,
VisaRenewal = @VisaRenewal,
VisaJobTitle = @VisaJobTitle,
EmployeeLocked = @EmployeeLocked,
SentCVDate = @SentCVDate,
TaxInfoDate = @TaxInfoDate,
HolidayPeriod = @HolidayPeriod,
PayslipFormat = @PayslipFormat,
ReasonForNotSigningUp = @ReasonForNotSigningUp,
RecruitmentConsultant = @RecruitmentConsultant,
InquiryTypeID = @InquiryTypeID,
ActiveDate = @ActiveDate,
VisaHasBeenSighted = @VisaHasBeenSighted,
SetToLesterSponsored = @SetToLesterSponsored,
DimiaMinimum = @DimiaMinimum,
VisaNominatedDate = @VisaNominatedDate,
PreviousSponsorshipDate = @PreviousSponsorshipDate,
CancelledDate457 = @CancelledDate457,
ReasonForLeavingID = @ReasonForLeavingID,
EmploymentStartDate = @EmploymentStartDate,
Occupation = @Occupation,
AccrueLeaveYesNo = @AccrueLeaveYesNo,
OldVisaID = @OldVisaID
WHERE (EmployeeID = @EmployeeID)
July 17, 2007 at 2:07 am
thanks Olegs but you didn't read my post.
July 17, 2007 at 2:11 am
>I have tried my code on another table and it works fine
Can you shown scripts both tables?
July 17, 2007 at 3:02 am
ok this proc works fne
CREATE PROCEDURE sp_tbrnet_updateNotifyTable (
@NFID int,
@NFDateAdded datetime,
@NFUserAdded varchar(50),
@NFDateNotify datetime,
@NFNoted bit,
@NFUserNoted varchar(50),
@NFNNID int,
@NFNote varchar(8000),
@NFEmpID int,
@NFNotedWasOn bit,
@NFNotedDate datetime,
@Original_NFID int,
@Original_NFDateAdded datetime,
@Original_NFUserAdded varchar(50),
@Original_NFDateNotify datetime,
@Original_NFNoted bit,
@Original_NFUserNoted varchar(50),
@Original_NFNNID int,
@Original_NFNote varchar(8000),
@Original_NFEmpID int,
@Original_NFNotedWasOn bit,
@Original_NFNotedDate datetime
)
AS
UPDATE NotifyTable
SET
NFDateAdded = @NFDateAdded,
NFUserAdded = @NFUserAdded,
NFDateNotify = @NFDateNotify,
NFNoted = @NFNoted,
NFUserNoted = @NFUserNoted,
NFNNID = @NFNNID,
NFNote = @NFNote,
NFEmpID = @NFEmpID,
NFNotedWasOn = @NFNotedWasOn,
NFNotedDate = @NFNotedDate
WHERE (NFID = @NFID) AND
(NFID = @Original_NFID OR @Original_NFID IS NULL AND NFID IS NULL) AND
(NFDateAdded = @Original_NFDateAdded OR @Original_NFDateAdded IS NULL AND NFDateAdded IS NULL) AND
(NFUserAdded = @Original_NFUserAdded OR @Original_NFUserAdded IS NULL AND NFUserAdded IS NULL) AND
(NFDateNotify = @Original_NFDateNotify OR @Original_NFDateNotify IS NULL AND NFDateNotify IS NULL) AND
(NFNoted = @Original_NFNoted OR @Original_NFNoted IS NULL AND NFNoted IS NULL) AND
(NFUserNoted = @Original_NFUserNoted OR @Original_NFUserNoted IS NULL AND NFUserNoted IS NULL) AND
(NFNNID = @Original_NFNNID OR @Original_NFNNID IS NULL AND NFNNID IS NULL) AND
(NFNote Like @Original_NFNote OR @Original_NFNote IS NULL AND NFNote IS NULL) AND
(NFEmpID = @Original_NFEmpID OR @Original_NFEmpID IS NULL AND NFEmpID IS NULL) AND
(NFNotedWasOn = @Original_NFNotedWasOn OR @Original_NFNotedWasOn IS NULL AND NFNotedWasOn IS NULL) AND
(NFNotedDate = @Original_NFNotedDate OR @Original_NFNotedDate IS NULL AND NFNotedDate IS NULL)
RETURN
July 17, 2007 at 5:03 am
Malcolm I am sorry. I bad understand English and also writting. I want to see scripts of creation tables which are using in both procedures.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply