%TYPE in SQL SERVER?

Question

Can we make parameter of a procedure of same type as that of data type of a column in a table in SQL SERVER ?

In Oracle we can do it as follow:

myVar IN mySchema.myTable.myColumn%TYPE

Answer 1

There is no direct equivalent of this Oracle feature in SQL Server.

Whilst not exactly the same thing, SQL Server 2005 onwards you can use CREATE TYPE and declare the column and the parameter of the same User Defined Type (UDT):

Creates an alias data type or a user-defined type in the current database. The implementation of an alias data type is based on a SQL Server native system type. A user-defined type is implemented through a class of an assembly in the Microsoft .NET Framework common language runtime (CLR). To bind a user-defined type to its implementation, the CLR assembly that contains the implementation of the type must first be registered in SQL Server by using CREATE ASSEMBLY.

An example:

CREATE TYPE SSN
FROM varchar(11) NOT NULL ;

[BUT, I very rarely create user defined types in SQL Server, for the same reasoning as mentioned by @Martin Smith: "Changing the definition of UDTs is a real pain compared to just dynamically inferring it from the column datatype."]