Link to home
Start Free TrialLog in
Avatar of MiracleByDesign
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
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

Open in new window

Avatar of chapmandew
chapmandew
Flag of United States of America image

make it look something like this:

If @ecPreparedByPhoneTWS is not null Or len(ltrim(rtrim(@ecPreparedByPhoneTWS))) > 0

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
Avatar of MiracleByDesign
MiracleByDesign

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
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.


	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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you guys...I will try these suggestions and let you know.
It worked perfectly!!!!Thank you!