Solved

MS SQL cursor problem checking for not null or blank strings

Posted on 2008-10-03
10
520 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:MiracleByDesign
  • 4
  • 4
  • 2
10 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
make it look something like this:

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

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
0
 

Author Comment

by:MiracleByDesign
Comment Utility
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
0
 

Author Comment

by:MiracleByDesign
Comment Utility
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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
Comment Utility
Alternatively you could do something like this.  It will require no cursor and be MUCH faster.

update rc_EmployerChanges

set   TelephoneTWS       = CASE when len(Telephone)>0       then 'T' else 'F' end  -- is F what you want alternatively?

     ,SECTelephoneTWS    = case when len(SecTelephone)>0    then 'T' else 'F' end

....

....
 

--repeat all columns like you have in your cursor

Open in new window

0
 

Author Comment

by:MiracleByDesign
Comment Utility
Thank you guys...I will try these suggestions and let you know.
0
 

Author Closing Comment

by:MiracleByDesign
Comment Utility
It worked perfectly!!!!Thank you!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now