• Article

Constants (Database Engine)

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. The following tables shows some examples of how constants are used.

Constants used in

Example

Character strings

'O''Brien'

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

Unicode strings

N'Michl'

Binary string constants

0x12Ef

0x69048AEFDD010E

bit constants

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 by using the COLLATE clause, for example:

'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. The following are some examples:

  • As a constant value in an arithmetic expression:

    SELECT Price + $.10
FROM MyTable

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

    SELECT *
FROM MyTable
WHERE LastName = 'O''Brien'

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

    SET @DecimalVar = -1200.02

  • As the data value that should be put 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