How to Use SQL Variables

Although being able to manipulate literals and other kinds of constants while dealing with a SQL database gives you great power, having variables is helpful, too. In many cases, you’d need to do much more work if you didn’t have variables. A variable, by the way, is a quantity that has a value that can change. Look at the following example to see why variables are valuable.

Suppose that you’re a retailer who has several classes of customers. You give your high-volume customers the best price, your medium-volume customers the next best price, and your low-volume customers the highest price. You want to index all prices to your cost of goods.

For your F-35 product, you decide to charge your high-volume customers (Class C) 1.4 times your cost of goods. You charge your medium-volume customers (Class B) 1.5 times your cost of goods, and you charge your low-volume customers (Class A) 1.6 times your cost of goods.

You store the cost of goods and the prices that you charge in a table named PRICING. To implement your new pricing structure, you issue the following SQL commands:

UPDATE PRICING
 SET Price = Cost * 1.4
 WHERE Product = 'F-35'
  AND Class = 'C' ;
UPDATE PRICING
 SET Price = Cost * 1.5
 WHERE Product = 'F-35'
  AND Class = 'B' ;
UPDATE PRICING
 SET Price = Cost * 1.6
 WHERE Product = 'F-35'
  AND Class = 'A' ;

This code is fine and meets your needs — for now. But if aggressive competition begins to eat into your market share, you may need to reduce your margins to remain competitive. To change your margins, you need to enter code something like this:

UPDATE PRICING
 SET Price = Cost * 1.25
 WHERE Product = 'F-35'
  AND Class = 'C' ;
UPDATE PRICING
 SET Price = Cost * 1.35
 WHERE Product = 'F-35'
  AND Class = 'B' ;
UPDATE PRICING
 SET Price = Cost * 1.45
 WHERE Product = 'F-35'
  AND Class = 'A' ;

If you’re in a volatile market, you may need to rewrite your SQL code repeatedly. This task can become tedious, particularly if prices appear in multiple places in your code. You can minimize your work by replacing literals (such as 1.45) with variables (such as :multiplierA). Then you can perform your updates as follows:

UPDATE PRICING
 SET Price = Cost * :multiplierC
 WHERE Product = 'F-35'
  AND Class = 'C' ;
UPDATE PRICING
 SET Price = Cost * :multiplierB
 WHERE Product = 'F-35'
  AND Class = 'B' ;
UPDATE PRICING
 SET Price = Cost * :multiplierA
 WHERE Product = 'F-35'
  AND Class = 'A' ;

Now whenever market conditions force you to change your pricing, you need to change only the values of the variables :multiplierC, :multiplierB, and :multiplierA. These variables are parameters that pass to the SQL code, which then uses the variables to compute new prices.

Sometimes variables used in this way are called parameters or host variables. Variables are called parameters if they appear in applications written in SQL module language. They’re called host variables when they’re used in embedded SQL.

Embedded SQL means that SQL statements are embedded into the code of an application written in a host language. Alternatively, you can use SQL module language to create an entire module of SQL code. The host language application then calls the module. Either method can give you the capabilities that you want. The approach that you use depends on your SQL implementation.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com