asked on

How can i assign datatype to variable same as column data type in SQL Server Procedure

In SQL server's code i.e. procedure or function, how can i dynamically assign datatype to variable same as datatype of some column of some table.

Like we have in oracle we can write

myBeer Beers.name%TYPE;

so, mBeer variable has same datatype as "name" column of Beers table.

And if we change datatype in table, related code do not require to change.

Can we do same thing in SQL server 2005 / 2008?

Jarrod

In MS SQL this i not permitted

see: (for conversion possibilities)
http://msdn.microsoft.com/en-us/library/ms187928.aspx

lof

It depends what are you doing with the values. You may still need some conversion but in some cases you will achieve what you are asking for with SQL_VARIANT type

have a look at those links
http://msdn.microsoft.com/en-us/library/ms173829.aspx
http://msdn.microsoft.com/en-us/library/ms181071.aspx

ferik

ASKER

What all i want to do is to avoid assign static data type to any variable in stored proc or function. Let's say i have to store value of some column in variable, say its data type is nvarchar(10). I have mention it in table definition as column data type and in all stored proc where i need to fetch value of this column.

My point is, if i want to make it nvarchar(100) in table, then i have to go to each stored proc to change data type.

Same case for converting int to float, nvarchar to datetime etc.

ASKER CERTIFIED SOLUTION

lof

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial