Solved

MS SQL cursor problem checking for not null or blank strings

Posted on 2008-10-03
10
521 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
ID: 22636235
make it look something like this:

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

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22636268
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
ID: 22636286
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:MiracleByDesign
ID: 22637824
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
ID: 22637925
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22637986
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
ID: 22638117
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
ID: 22638143
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
ID: 22638398
Thank you guys...I will try these suggestions and let you know.
0
 

Author Closing Comment

by:MiracleByDesign
ID: 31502857
It worked perfectly!!!!Thank you!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
2016 SQL Licensing 7 41
VB.net and sql server 4 36
TSQL query to generate xml 4 35
Return 0 on SQL count 24 30
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

810 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