Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.