Export (0) Print
Expand All

Using Constants

SQL Server 2000

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2015 Microsoft