Reference Manual

Home

Reference Manual

Chapter 1: System and User-Defined Datatypes

Declaring the Datatype of a Column, Variable, or Parameter

You must declare the datatype for a column, local variable, or parameter. The datatype can be any of the system-supplied datatypes or any user-defined datatype in the database.

Declaring the Datatype for a Column in a Table

Use the following syntax to declare the datatype of a new column in a create table or an alter table statement:

create table [[database.]owner.]table_name 
(column_name datatype [identity | not null | null]
    [, column_name datatype [identity | not null |
        null]]...)

alter table [[database.]owner.]table_name 
add column_name datatype [identity | null 
    [, column_name datatype [identity | null]...

For example:

create table sales_daily
    (stor_id char(4)not null,
     ord_num numeric(10,0)identity,
     ord_amt money null)

Declaring the Datatype for a Local Variable in a Batch or Procedure

Use the following syntax to declare the datatype for a local variable in a batch or stored procedure:

declare @variable_name datatype 
[, @variable_name datatype]...

For example:

declare @hope money

Declaring the Datatype for a Parameter in a Stored Procedure

Use the following syntax to declare the datatype for a parameter in a stored procedure:

create procedure [owner.]procedure_name [;number] [[(]@parameter_name datatype [= default] [output] 
    [,@parameter_name datatype [= default] 
        [output]]...[)]]
[with recompile] 
as SQL_statements

For example:

create procedure auname_sp @auname varchar(40) 
as 
    select au_lname, title, au_ord 
    from authors, titles, titleauthor 
    where @auname = au_lname 
    and authors.au_id = titleauthor.au_id 
    and titles.title_id = titleauthor.title_id

Determining the Datatype of a Literal

You cannot declare the datatype of a literal. Adaptive Server treats all character literals as varchar. Numeric literals entered with E notation are treated as float; all others are treated as exact numerics:

Literals between 2³¹ - 1 and -2³¹ with no decimal point are treated as integer.
Literals that include a decimal point, or that fall outside the range for integers, are treated as numeric.
Note: , To preserve backward compatibility, use E notation for numeric literals that should be treated as float.


	Expand Search