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

Posted on 2010-01-08
Medium Priority
Last Modified: 2012-05-08
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?
Question by:ferik
  • 2
LVL 13

Expert Comment

ID: 26207853
In MS SQL this i not permitted

see: (for conversion possibilities)

LVL 10

Expert Comment

ID: 26219255
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

Author Comment

ID: 26281309
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.
LVL 10

Accepted Solution

lof earned 1000 total points
ID: 26281776
If all you want is to fetch value consider following example stored procedure.

create procedure Test @value sql_variant
      select @value

it will tak argument of (almost) any type. Here is example

declare @var1 varchar(10)
declare @var2 varchar(100)
declare @var3 int
declare @var4 float

set @var1 = 'abc'
set @var2 = 'abcdefghi'
set @var3 = 1
set @var4 = .5

exec Test @var1
exec Test @var2
exec Test @var3
exec Test @var4

But you need to remember that inside the value is of type sql_variant.
If it is only for Varchar variables you may always use varchar(max) as parameter type and then any column with varchar type will fit.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Screencast - Getting to Know the Pipeline

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question