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

Posted on 2010-01-08
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


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
    LVL 13

    Expert Comment

    In MS SQL this i not permitted

    see: (for conversion possibilities)

    LVL 10

    Expert Comment

    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

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now