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
```

Using Constants

Using Constants in Transact-SQL

See Also