Specifying a Parameter Data Type
Parameters in a stored procedure are defined with a data type, much as a column in a table is defined. A stored procedure parameter can be defined with any of the SQL Server 2005 data types, except the table data type. Stored procedure parameters can also be defined with CLR user-defined types and Transact-SQL alias data types.
The cursor data type can be used only as an OUTPUT parameter to a stored procedure. For more information about using cursor variables, see Scope of Transact-SQL Cursor Names.
The data type of a parameter determines the type and range of values that are accepted for the parameter. For example, if you define a parameter with a tinyint data type, only numeric values ranging from 0 to 255 are accepted. An error is returned if a stored procedure is executed with a value incompatible with the data type.