declare variable based on existing column

Is it possible to declare a variable based on an existing column?
Instead of:
DECLARE @.myvariable VARCHAR(20)
Use:
DECLARE @.myvariable mytable.mycolumn%type
WHERE
Table MYTABLE has column MYCOLUMN of data type VARCHAR(20)Not without doing the entire operation in dynamic SQL -- in other words, not
easily and probably not a great idea. Why would you want that
functionality?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:0DF1A88B-C787-4BA1-BA24-FFB7F4630E57@.microsoft.com...
> Is it possible to declare a variable based on an existing column?
> Instead of:
> DECLARE @.myvariable VARCHAR(20)
> Use:
> DECLARE @.myvariable mytable.mycolumn%type
> WHERE
> Table MYTABLE has column MYCOLUMN of data type VARCHAR(20)
>|||ORABLE has that functionality. I admit it's a nice thing, especially when
putting together smaller systems where data types change.
What about using a SQLVariant? I've rarely used that ... does that
Internally store the type (likea VB/JScript type deal)? If so, I'd immagine
that'd work in most scenarios ... not sure how it rates on the Best PRactice
scale though ...
-- Alex
"Adam Machanic" wrote:

> Not without doing the entire operation in dynamic SQL -- in other words, n
ot
> easily and probably not a great idea. Why would you want that
> functionality?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:0DF1A88B-C787-4BA1-BA24-FFB7F4630E57@.microsoft.com...
>
>|||"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:ACC91FFB-ADC5-4DD8-9966-05B78F22422C@.microsoft.com...
> What about using a SQLVariant? I've rarely used that ... does that
> Internally store the type (likea VB/JScript type deal)? If so, I'd
immagine
Yes, it's very similar to those languages' variant datatypes -- and just
like using them, it has lots of pitfalls... I wouldn't use it in production
code, personally, although I have found it useful in a few utility
operations -- but I had to work around a lot of problems, especially dealing
with datetime data.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||The larger the system, the more useful this is.
And its not even the data type changing, but more often the size of VARCHAR
fields for example. You design your system with LAST_NAME as VARCHAR(50),
then realize you need VARCHAR(1000). To make this change in SQL Server, it
seems that you would have to go through all your code to change any local
variables that are @.LAST_NAME. If you could base the declaration on the tabl
e
column, you would not have to do this.
In addition, it is good for global standardization of data types and sizes.
"Alex Papadimoulis" wrote:
> ORABLE has that functionality. I admit it's a nice thing, especially when
> putting together smaller systems where data types change.
> What about using a SQLVariant? I've rarely used that ... does that
> Internally store the type (likea VB/JScript type deal)? If so, I'd immagi
ne
> that'd work in most scenarios ... not sure how it rates on the Best PRacti
ce
> scale though ...
> -- Alex
> "Adam Machanic" wrote:
>|||sqlwish@.microsoft.com
Ask for DOMAINs, an ANSI SQL feature (Google for more info on it) -- it's
definitely at the top of my wishlist, too.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:332D49B5-1442-46BF-A821-77C6FF61D4BA@.microsoft.com...
> The larger the system, the more useful this is.
> And its not even the data type changing, but more often the size of
VARCHAR
> fields for example. You design your system with LAST_NAME as VARCHAR(50),
> then realize you need VARCHAR(1000). To make this change in SQL Server, it
> seems that you would have to go through all your code to change any local
> variables that are @.LAST_NAME. If you could base the declaration on the
table
> column, you would not have to do this.
> In addition, it is good for global standardization of data types and
sizes.
> "Alex Papadimoulis" wrote:
>
when
immagine
PRactice
words, not|||You could use sp_addtype to create your own types which are stored in
systypes, then you can define data table columns, sp and udf parameters, and
variables using the user defined types.
in addition, INFORMATION_SCHEMA.COLUMNS contains the columns DOMAIN_CATALOG,
DOMAIN_SCHEMA, and DOMAIN_NAME which can be used to tables that need to be
altered to accomodate the changed type definition. you can also do a search
on syscomments to find any additional objects that need to be recompiled in
addition to the table alterations.
note: sp_rename can be used to rename a user defined type.
"Bevo" wrote:
> The larger the system, the more useful this is.
> And its not even the data type changing, but more often the size of VARCHA
R
> fields for example. You design your system with LAST_NAME as VARCHAR(50),
> then realize you need VARCHAR(1000). To make this change in SQL Server, it
> seems that you would have to go through all your code to change any local
> variables that are @.LAST_NAME. If you could base the declaration on the ta
ble
> column, you would not have to do this.
> In addition, it is good for global standardization of data types and sizes
.
> "Alex Papadimoulis" wrote:
>|||"Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
news:23455E9D-7788-4229-80DB-DD46292346BD@.microsoft.com...
> You could use sp_addtype to create your own types which are stored in
> systypes, then you can define data table columns, sp and udf parameters,
and
> variables using the user defined types.
> in addition, INFORMATION_SCHEMA.COLUMNS contains the columns
DOMAIN_CATALOG,
> DOMAIN_SCHEMA, and DOMAIN_NAME which can be used to tables that need to be
> altered to accomodate the changed type definition. you can also do a
search
> on syscomments to find any additional objects that need to be recompiled
in
> addition to the table alterations.
Brian,
sp_addtype is deprecated in the next version of SQL Server. I recommend
that you do not use it. Unfortunately, those DOMAIN columns don't provide
full DOMAIN support -- the ANSI domains, as I understand them, operate
similarly to the types that can be added with sp_addtype, but with much
greater flexibility and bound constraints.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||>> the ANSI domains, as I understand them, operate similarly to the types
Other than offering a syntactic shorthand, what else can ANSI domains do?
Would it offer anything meaningful in terms of simplification, flexibility
and utility of existing ANSI standard built-in types? If T-SQL UDTs support
binding of constraints, wouldn't it obviate the need for ANSI domains, if
that is the lacking provision?
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OLLBtY1GFHA.3536@.TK2MSFTNGP14.phx.gbl...
> Other than offering a syntactic shorthand, what else can ANSI domains do?
> Would it offer anything meaningful in terms of simplification, flexibility
> and utility of existing ANSI standard built-in types? If T-SQL UDTs
support
> binding of constraints, wouldn't it obviate the need for ANSI domains, if
> that is the lacking provision?
Two things:
A) Correct me if I'm wrong but I know of no way to alter a T-SQL UDT.
The ANSI Standard does provide ALTER DOMAIN syntax. The OP in this
situation, it appears, wants his variables to be able to mimic the same
datatype used in the table -- even if that datatype should change (e.g. if
changing business requirements mean that the datatype needs to support 100
bytes instead of 50). Is that possible with a T-SQL UDT as they are
currently implemented?
B) My understanding is that the constraints bound to ANSI DOMAINs extend
to variables declared of a domain datatype... so if I define a domain
INTBETWEEN1AND10, which is an integer with a constraint that it must be
between 1 and 10, that will be enforced even for local variables of that
type. That's not the case with T-SQL UDTs, is it?
If I'm wrong on both of these counts, then I regret not making much
heavier use of T-SQL UDTs in my work to date -- I've pretty much ignored the
feature as I have been under the impression that it's inflexible and doesn't
add value.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--