MiracleByDesign
asked on
MS SQL cursor problem checking for not null or blank strings
Hello Experts,
I would like you to view my code to see if you can determine the error if possible. I am populating a cursor and I need to check if each variable has data in it. If it does, I need to update another field with a "T" which is used to protect fields on the front-end of the application. The problem is all of my flag fields are being updated with a "T" whether there is data or not. Hopefully this will be quick for someone to catch my error but I need this before Tuesday if possible.
Thanks,
Miracle By Design
I would like you to view my code to see if you can determine the error if possible. I am populating a cursor and I need to check if each variable has data in it. If it does, I need to update another field with a "T" which is used to protect fields on the front-end of the application. The problem is all of my flag fields are being updated with a "T" whether there is data or not. Hopefully this will be quick for someone to catch my error but I need this before Tuesday if possible.
Thanks,
Miracle By Design
Begin Transaction
--Declare cursor columns
--RC_EmployerChanges cursor variables
Declare @ecTelephoneTWS varchar(14),
@ecSecTelephoneTWS varchar(14),
@ecPreparedByPhoneTWS varchar(14),
@ecEmailTWS varchar(60),
@ecContactFirstNameTWS varchar(60),
@ecContactLastNameTWS varchar(60),
@ecContactTitleTWS varchar(60),
@ecContactTelephoneTWS varchar(14),
@ecDiscontinuedDateTWS datetime,
@ecCeasedWagesDateTWS datetime,
--RC_EmployerRep cursor variables
@erContactFirstNameTWS varchar(60),
@erContactLastNameTWS varchar(60),
@erContactTitleTWS varchar(60),
@erContactTelephoneTWS varchar(14),
--RC_Leasing cursor variables
@leContactFirstNameTWS varchar(60),
@leContactLastNameTWS varchar(60),
@leContactTitleTWS varchar(60),
@leContactTelephoneTWS varchar(14),
--RC_Payroll cursor variables
@paContactFirstNameTWS varchar(60),
@paContactLastNameTWS varchar(60),
@paContactTitleTWS varchar(60),
@paContactTelephoneTWS varchar(14),
--RC_TransferAll cursor variables
@taContactFirstNameTWS varchar(60),
@taContactLastNameTWS varchar(60),
@taContactTitleTWS varchar(60),
@taContactTelephoneTWS varchar(14),
--RC_TrasnferPart cursor variables
@tpContactFirstNameTWS varchar(60),
@tpContactLastNameTWS varchar(60),
@tpContactTitleTWS varchar(60),
@tpContactTelephoneTWS varchar(14),
@tpRetainedContactFirstNameTWS varchar(60),
@tpRetainedContactLastNameTWS varchar(60),
@tpRetainedContactTitleTWS varchar(60),
@tpRetainedContactTelephoneTWS varchar(14),
@tpRetainedNumOfEmployeesTWS int
--cursor for RC_EmployerChanges
Declare ecTWS_Flags cursor Fast_Forward
For select Telephone, SecTelephone, PreparedByPhone, Email,
ContactFirstName, ContactLastName, ContactTitle, ContactTelephone, DiscontinuedDate,
CeasedWagesDate From RC_EmployerChanges
Open ecTWS_Flags
Fetch Next From ecTWS_Flags Into @ecTelephoneTWS, @ecSecTelephoneTWS, @ecPreparedByPhoneTWS,
@ecEmailTWS, @ecContactFirstNameTWS, @ecContactLastNameTWS, @ecContactTitleTWS,
@ecContactTelephoneTWS, @ecDiscontinuedDateTWS, @ecCeasedWagesDateTWS
While @@Fetch_Status = 0
Begin
If @ecTelephoneTWS is not null Or @ecTelephoneTWS <> ' '
Begin
Update RC_EmployerChanges
Set TelephoneTWS = 'T'
End
If @ecSecTelephoneTWS is not null Or @ecSecTelephoneTWS <> ' '
Begin
Update RC_EmployerChanges
Set SecTelephoneTWS = 'T'
End
If @ecPreparedByPhoneTWS is not null Or @ecPreparedByPhoneTWS <> ' '
Begin
Update RC_EmployerChanges
Set PreparedByPhoneTWS = 'T'
End
If @ecEmailTWS is not null Or @ecEmailTWS <> ' '
Begin
Update RC_EmployerChanges
Set EmailTWS = 'T'
End
If @ecContactFirstNameTWS is not null Or @ecContactFirstNameTWS <> ' '
Begin
Update RC_EmployerChanges
Set ContactFirstNameTWS = 'T'
End
If @ecContactLastNameTWS is not null Or @ecContactLastNameTWS <> ' '
Begin
Update RC_EmployerChanges
Set ContactLastNameTWS = 'T'
End
If @ecContactTitleTWS is not null Or @ecContactTitleTWS <> ' '
Begin
Update RC_EmployerChanges
Set ContactTitleTWS = 'T'
End
If @ecContactTelephoneTWS is not null Or @ecContactTelephoneTWS <> ' '
Begin
Update RC_EmployerChanges
Set ContactTelephoneTWS = 'T'
End
If @ecDiscontinuedDateTWS is not null Or @ecDiscontinuedDateTWS <> ' '
Begin
Update RC_EmployerChanges
Set DiscontinuedDateTWS = 'T'
End
If @ecCeasedWagesDateTWS is not null Or @ecCeasedWagesDateTWS <> ' '
Begin
Update RC_EmployerChanges
Set CeasedWagesDateTWS = 'T'
End
Fetch Next From ecTWS_Flags Into @ecTelephoneTWS, @ecSecTelephoneTWS, @ecPreparedByPhoneTWS,
@ecEmailTWS, @ecContactFirstNameTWS, @ecContactLastNameTWS, @ecContactTitleTWS,
@ecContactTelephoneTWS, @ecDiscontinuedDateTWS, @ecCeasedWagesDateTWS
End
Close ecTWS_Flags
Deallocate ecTWS_Flags
--cursor for RC_EmployerRep
Declare erTWS_Flags cursor Fast_Forward
For select ContactFirstName, ContactLastName, ContactTitle, ContactTelephone
From RC_EmployerRep
Open erTWS_Flags
Fetch Next From erTWS_Flags Into @erContactFirstNameTWS, @erContactLastNameTWS, @erContactTitleTWS, @erContactTelephoneTWS
While @@Fetch_Status = 0
Begin
If @erContactFirstNameTWS is not null Or @erContactFirstNameTWS <> ' '
Begin
Update RC_EmployerRep
Set ContactFirstNameTWS = 'T'
End
If @erContactLastNameTWS is not null Or @erContactLastNameTWS <> ' '
Begin
Update RC_EmployerRep
Set ContactLastNameTWS = 'T'
End
If @erContactTitleTWS is not null Or @erContactTitleTWS <> ' '
Begin
Update RC_EmployerRep
Set ContactTitleTWS = 'T'
End
If @erContactTelephoneTWS is not null Or @erContactTelephoneTWS <> ' '
Begin
Update RC_EmployerRep
Set ContactTelephoneTWS = 'T'
End
Fetch Next From erTWS_Flags Into @erContactFirstNameTWS, @erContactLastNameTWS, @erContactTitleTWS, @erContactTelephoneTWS
End
Close erTWS_Flags
Deallocate erTWS_Flags
--cursor for RC_Leasing
Declare leTWS_Flags cursor Fast_Forward
For select ContactFirstName, ContactLastName, ContactTitle, ContactTelephone
From RC_Leasing
Open leTWS_Flags
Fetch Next From leTWS_Flags Into @leContactFirstNameTWS, @leContactLastNameTWS, @leContactTitleTWS, @leContactTelephoneTWS
While @@Fetch_Status = 0
Begin
If @leContactFirstNameTWS is not null Or @leContactFirstNameTWS <> ' '
Begin
Update RC_Leasing
Set ContactFirstNameTWS = 'T'
End
If @leContactLastNameTWS is not null Or @leContactLastNameTWS <> ' '
Begin
Update RC_Leasing
Set ContactLastNameTWS = 'T'
End
If @leContactTitleTWS is not null Or @leContactTitleTWS <> ' '
Begin
Update RC_Leasing
Set ContactTitleTWS = 'T'
End
If @leContactTelephoneTWS is not null Or @leContactTelephoneTWS <> ' '
Begin
Update RC_Leasing
Set ContactTelephoneTWS = 'T'
End
Fetch Next From leTWS_Flags Into @leContactFirstNameTWS, @leContactLastNameTWS, @leContactTitleTWS, @leContactTelephoneTWS
End
Close leTWS_Flags
Deallocate leTWS_Flags
--cursor for RC_Payroll
Declare paTWS_Flags cursor Fast_Forward
For select ContactFirstName, ContactLastName, ContactTitle, ContactTelephone
From RC_Payroll
Open paTWS_Flags
Fetch Next From paTWS_Flags Into @paContactFirstNameTWS, @paContactLastNameTWS, @paContactTitleTWS, @paContactTelephoneTWS
While @@Fetch_Status = 0
Begin
If @paContactFirstNameTWS is not null Or @paContactFirstNameTWS <> ' '
Begin
Update RC_Payroll
Set ContactFirstNameTWS = 'T'
End
If @paContactLastNameTWS is not null Or @paContactLastNameTWS <> ' '
Begin
Update RC_Payroll
Set ContactLastNameTWS = 'T'
End
If @paContactTitleTWS is not null Or @paContactTitleTWS <> ' '
Begin
Update RC_Payroll
Set ContactTitleTWS = 'T'
End
If @paContactTelephoneTWS is not null Or @paContactTelephoneTWS <> ' '
Begin
Update RC_Payroll
Set ContactTelephoneTWS = 'T'
End
Fetch Next From paTWS_Flags Into @paContactFirstNameTWS, @paContactLastNameTWS, @paContactTitleTWS, @paContactTelephoneTWS
End
Close paTWS_Flags
Deallocate paTWS_Flags
--cursor for RC_TransferAll
Declare taTWS_Flags cursor Fast_Forward
For select ContactFirstName, ContactLastName, ContactTitle, ContactTelephone
From RC_TransferAll
Open taTWS_Flags
Fetch Next From taTWS_Flags Into @taContactFirstNameTWS, @taContactLastNameTWS, @taContactTitleTWS, @taContactTelephoneTWS
While @@Fetch_Status = 0
Begin
If @taContactFirstNameTWS is not null Or @taContactFirstNameTWS <> ' '
Begin
Update RC_TransferAll
Set ContactFirstNameTWS = 'T'
End
If @taContactLastNameTWS is not null Or @taContactLastNameTWS <> ' '
Begin
Update RC_TransferAll
Set ContactLastNameTWS = 'T'
End
If @taContactTitleTWS is not null Or @taContactTitleTWS <> ' '
Begin
Update RC_TransferAll
Set ContactTitleTWS = 'T'
End
If @taContactTelephoneTWS is not null Or @taContactTelephoneTWS <> ' '
Begin
Update RC_TransferAll
Set ContactTelephoneTWS = 'T'
End
Fetch Next From taTWS_Flags Into @taContactFirstNameTWS, @taContactLastNameTWS, @taContactTitleTWS, @taContactTelephoneTWS
End
Close taTWS_Flags
Deallocate taTWS_Flags
--cursor for RC_TransferPart
Declare tpTWS_Flags cursor Fast_Forward
For select ContactFirstName, ContactLastName, ContactTitle, ContactTelephone,
RetainedContactFirstName, RetainedContactLastName, RetainedContactTitle,
RetainedContactTelephone, RetainedNumOfEmployees
From RC_TransferPart
Open tpTWS_Flags
Fetch Next From tpTWS_Flags Into @tpContactFirstNameTWS, @tpContactLastNameTWS, @tpContactTitleTWS, @tpContactTelephoneTWS,
@tpRetainedContactFirstNameTWS, @tpRetainedContactLastNameTWS, @tpRetainedContactTitleTWS, @tpRetainedContactTelephoneTWS,
@tpRetainedNumOfEmployeesTWS
While @@Fetch_Status = 0
Begin
If @tpContactFirstNameTWS is not null Or @tpContactFirstNameTWS <> ' '
Begin
Update RC_TransferPart
Set ContactFirstNameTWS = 'T'
End
If @tpContactLastNameTWS is not null Or @tpContactLastNameTWS <> ' '
Begin
Update RC_TransferPart
Set ContactLastNameTWS = 'T'
End
If @tpContactTitleTWS is not null Or @tpContactTitleTWS <> ' '
Begin
Update RC_TransferPart
Set ContactTitleTWS = 'T'
End
If @tpContactTelephoneTWS is not null Or @tpContactTelephoneTWS <> ' '
Begin
Update RC_TransferPart
Set ContactTelephoneTWS = 'T'
End
If @tpRetainedContactFirstNameTWS is not null Or @tpRetainedContactFirstNameTWS <> ' '
Begin
Update RC_TransferPart
Set RetainedContactFirstNameTWS = 'T'
End
If @tpRetainedContactLastNameTWS is not null Or @tpRetainedContactLastNameTWS <> ' '
Begin
Update RC_TransferPart
Set RetainedContactLastNameTWS = 'T'
End
If @tpRetainedContactTitleTWS is not null Or @tpRetainedContactTitleTWS <> ' '
Begin
Update RC_TransferPart
Set RetainedContactTitleTWS = 'T'
End
If @tpRetainedContactTelephoneTWS is not null Or @tpRetainedContactTelephoneTWS <> ''
Begin
Update RC_TransferPart
Set RetainedContactTelephoneTWS = 'T'
End
If @tpRetainedNumOfEmployeesTWS is not null Or @tpRetainedNumOfEmployeesTWS <> ' '
Begin
Update RC_TransferPart
Set RetainedNumOfEmployeesTWS = 'T'
End
Fetch Next From tpTWS_Flags Into @tpContactFirstNameTWS, @tpContactLastNameTWS, @tpContactTitleTWS, @tpContactTelephoneTWS,
@tpRetainedContactFirstNameTWS, @tpRetainedContactLastNameTWS, @tpRetainedContactTitleTWS, @tpRetainedContactTelephoneTWS,
@tpRetainedNumOfEmployeesTWS
End
Close tpTWS_Flags
Deallocate tpTWS_Flags
Commit Transaction
Without getting into the fact that you don't need a cursor at all, you have no where clause on your update statements so it's updating every record every tim
len truncates trailing spaces. So no need to trim.
select len(null),len(''),len(' ')
Your check could be as simple as len(@Variable)>0, but again, this can be written with a single update statement (per table) and you don't need a cursor at all
select len(null),len(''),len(' ')
Your check could be as simple as len(@Variable)>0, but again, this can be written with a single update statement (per table) and you don't need a cursor at all
ASKER
Chap...I will try your suggestion. Brandon...I do need a cursor and only posted a portion of the 35 page procedure that is not working correctly. There is no where clause because my condition is the if statement. I need that field to always have a value of "T" if any type of data is found in the variable
ASKER
Chap...I tried your suggestion but it did not work. Any other suggestions from anyone? Once again certain fields must be check and there is a disable flag if data is found in the field. The problem is the cursor is updating everything without considering the if statement.
here is the deal...since you're using a cursor, something might be updated on one go around (all of the records in the table), and when another rec is cycled in the cursor, it may not hit again...but it did the first time, so all of your values got updated...make sense?
Maybe this is what you want, but I'll say again about there where clause.
You look through each record in rc_EmployerChanges (using your cursor).
then, you update EVERY RECORD in rc_employerChanges using this:
If @ecTelephoneTWS is not null Or @ecTelephoneTWS <> ' '
Begin
Update RC_EmployerChanges
Set TelephoneTWS = 'T'
End
because you are not updating based on the current record in your cursor with a where clause.
You look through each record in rc_EmployerChanges (using your cursor).
then, you update EVERY RECORD in rc_employerChanges using this:
If @ecTelephoneTWS is not null Or @ecTelephoneTWS <> ' '
Begin
Update RC_EmployerChanges
Set TelephoneTWS = 'T'
End
because you are not updating based on the current record in your cursor with a where clause.
Declare ecTWS_Flags cursor Fast_Forward
For select Telephone, SecTelephone, PreparedByPhone, Email,
ContactFirstName, ContactLastName, ContactTitle, ContactTelephone, DiscontinuedDate,
CeasedWagesDate From RC_EmployerChanges
Open ecTWS_Flags
Fetch Next From ecTWS_Flags Into @ecTelephoneTWS, @ecSecTelephoneTWS, @ecPreparedByPhoneTWS,
@ecEmailTWS, @ecContactFirstNameTWS, @ecContactLastNameTWS, @ecContactTitleTWS,
@ecContactTelephoneTWS, @ecDiscontinuedDateTWS, @ecCeasedWagesDateTWS
While @@Fetch_Status = 0
Begin
If @ecTelephoneTWS is not null Or @ecTelephoneTWS <> ' '
Begin
Update RC_EmployerChanges
Set TelephoneTWS = 'T'
End
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you guys...I will try these suggestions and let you know.
ASKER
It worked perfectly!!!!Thank you!
If @ecPreparedByPhoneTWS is not null Or len(ltrim(rtrim(@ecPrepare