Datatypes of columns, variables, or parameters

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.

To declare the datatype of a new column in a create table or alter table statement, use:

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)

You can also declare the datatype of a new column in a select into statement, use convert or cast:

select convert(double precision, x), cast ( int, y) into 
    newtable from oldtable

To declare the datatype for a local variable in a batch or stored procedure, use:

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

For example:

declare @hope money

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 

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.

  To preserve backward compatibility, use E notation for numeric literals that should be treated as float.

In versions of Adaptive Server earlier than 12.5.1, when the client’s character set was different from the server’s character set, conversions were generally enabled to allow the text of SQL queries to be converted to the server’s character set before being processed. If any character could not be converted because it could not be represented in the server’s character set, the entire query was rejected. This character set “bottleneck” has been removed as of Adaptive Server version 12.5.1.

You cannot declare the datatype of a character literal. Adaptive Server treats character literals as varchar, except those that contain characters that cannot be converted to the server’s default character set. Such literals are treated as univarchar. This makes it possible to perform such queries as selecting unichar data in a server configured for “iso_1” using a “sjis” (Japanese) client. For example:

Since the character literal cannot be represented using the char datatype (in “iso_1”), it is promoted to the unichar datatype, and the query succeeds.