Using Constants

A constant is a symbol that represents a specific data value. The format of a constant depends on the data type of the value it represents. Constants are also called literals. Some examples of constants are:

• Character strings:

  'O''Brien'

  'The level for job_id: %d should be between %d and %d.'

    

• Unicode strings:

  N'Michél'

    

• Binary string constants:

  0x12Ef

  0x69048AEFDD010E

    

• bit constants are represented by the numbers 0 or 1.
• datetime constants:

  'April 15, 1998'

  '04/15/98'

  '14:30:24'

  '04:24 PM'

    

• integer constants:

  1894

  2

    

• decimal constants:

  1894.1204

  2.0

    

• float and real constants:

  101.5E5

  0.5E-2

    

• money constants:

  $12

  $542023.14

    

• uniqueidentifier constants:

  0xff19966f868b11d0b42d00c04fc964ff

  '6F9619FF-8B86-D011-B42D-00C04FC964FF'

    

For numeric constants, to specify the sign of the numeric value use the unary + and - operators:

+$156.45

-73.52E8

-129.42

+442

Using Constants in Transact-SQL

In Transact-SQL, constants can be used in many ways. Here are some examples:

• As a constant value in an arithmetic expression:

  SELECT Price + $.10

  FROM MyTable

    

• As the data value a column is compared against in a WHERE clause:

  SELECT *

  FROM MyTable

  WHERE LastName = 'O''Brien'

    

• As the data value to be placed in a variable:

  SET @DecimalVar = -1200.02

    

• As the data value that should be placed in a column of the current row. This is specified with the SET clause of the UPDATE statement or the VALUES clause of an INSERT statement:

  UPDATE MyTable

  SET Price = $99.99

  WHERE PartNmbr = 1234

    

  INSERT INTO MyTable VALUES (1235, $88.88)

    

• As the character string that specifies the text of the message issued by a PRINT or RAISERROR statement:

  PRINT 'This is a message.'

    

• As the value to test for in a conditional statement such as an IF statement or CASE functions:

  IF (@@SALESTOTAL > $100000.00)

  EXECUTE Give_Bonus_Procedure

See Also