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:
- Binary string constants:
- 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:
- decimal constants:
- float and real constants:
- money constants:
- uniqueidentifier constants:
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