To find inequalities within a column, how can I compare an input variable with each row of data in a particular column, check to see which value is greater, stop and perform a task if the input is greater than the row, or move on to the next if it's not?

    Requires Free Membership to View

SQL is a set-based language. While SQL does provide for cursors, you may research them yourself, as you will find no help from me on using them. Most database systems also provide for stored procedures, which would allow you both to write procedural code to "move on" from one row of a table to the next, and to "perform a task" when a condition is detected. However, you will again find no help from me on writing stored procedures.

A set-based solution is characterized by an approach in which you specify which rows you want, and typically this set of rows all satisfy a specific condition. This is the way SQL was designed to work.

An example which involves "performing a task" is an UPDATE. Suppose your input variable is 500, which represents an amount of five hundred dollars, and you want to compare this to a particular column called CustomerReturns in a table called StaffSalesTotals, and if 500 is greater than the value in the CustomerReturns column, this indicates that the sales person represented by this row has not exceeded the threshold for customer returns, and therefore the action that you wish to take is to set the value of the LowReturnsBonus column to 50, representing a special 50 dollar bonus amount for this salesperson.

update StaffSalesTotals
   set LowReturnsBonus = 50
 where CustomerReturns < 500   

Note in particular that there is nothing in this solution which involves "stopping" or "moving on to the next row" -- these concepts are meaningless in a set-based solution. All rows which meet the specified condition are updated simultaneously.

This was first published in April 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: