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

Character and Unicode constants are assigned the default collation of the current database, unless you assign a specific collation using the COLLATE clause:

'abc' COLLATE French_CI_AI
N'lustig' COLLATE German_Phonebook_CS_AS

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

Constants

INSERT

Expressions

LIKE

Operators

PRINT

ALTER TABLE

RAISERROR

CREATE TABLE

UPDATE

DELETE

WHERE