cast question

if i declare a variable within a stored procedure as a varchar, then later
cast it to an integer- will it act as an integer for all subsequent
references after the cast?
example:
declare @.myVar varchar(2)
set @.myVar = '2'
set @.myVar = cast(@.myVar as integer)
select @.myVar -- what data type is my variable now? is it an integer
because of the previous cast?
or would i need to specify: "select
cast(@.myVar as integer)" in order for it to be considered an integer?
thanks much,
jTJT,
DECLARE @.MYVAR VARCHAR(2)
SET @.MYVAR = '2'
SELECT ISNUMERIC(@.MYVAR)
SELECT 'THIS IS A TEST ' + @.MYVAR
SET @.MYVAR = CAST(@.MYVAR AS INTEGER)
SELECT ISNUMERIC(@.MYVAR)
SELECT 'THIS IS A TEST ' + @.MYVAR
RESULTS:
1
THIS IS A TEST 2
1
THIS IS A TEST 2
HTH
Jerry
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>|||Since you declared it to be varchar(2), that's how it will remain for the
batch.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
if i declare a variable within a stored procedure as a varchar, then later
cast it to an integer- will it act as an integer for all subsequent
references after the cast?
example:
declare @.myVar varchar(2)
set @.myVar = '2'
set @.myVar = cast(@.myVar as integer)
select @.myVar -- what data type is my variable now? is it an integer
because of the previous cast?
or would i need to specify: "select
cast(@.myVar as integer)" in order for it to be considered an integer?
thanks much,
jT|||Note however that this will cause the string concatenation to fail where the
others worked:
SELECT 'THIS IS A TEST ' + CAST(@.MYVAR AS INTEGER)
Results:
Server: Msg 245, Level 16, State 1, Line 7
Syntax error converting the varchar value 'THIS IS A TEST ' to a column of
data type int.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsPhT1zFHA.3896@.TK2MSFTNGP10.phx.gbl...
> JT,
> DECLARE @.MYVAR VARCHAR(2)
> SET @.MYVAR = '2'
> SELECT ISNUMERIC(@.MYVAR)
> SELECT 'THIS IS A TEST ' + @.MYVAR
> SET @.MYVAR = CAST(@.MYVAR AS INTEGER)
> SELECT ISNUMERIC(@.MYVAR)
> SELECT 'THIS IS A TEST ' + @.MYVAR
> RESULTS:
> --
> 1
>
> --
> THIS IS A TEST 2
> --
> 1
> --
> THIS IS A TEST 2
> HTH
> Jerry
> "JT" <jt@.nospam.com> wrote in message
> news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||thanks much!
jT
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e1hiGX1zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Note however that this will cause the string concatenation to fail where
the
> others worked:
> SELECT 'THIS IS A TEST ' + CAST(@.MYVAR AS INTEGER)
> Results:
> Server: Msg 245, Level 16, State 1, Line 7
> Syntax error converting the varchar value 'THIS IS A TEST ' to a column of
> data type int.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsPhT1zFHA.3896@.TK2MSFTNGP10.phx.gbl...
integer
>|||Just to add, you are casting the value in @.myVar to integer, not the @.myVar
variable. Note also that you don't have to cast it to an integer just get
use it as an integer. If you said
select 1 + '1'
It will return:
2
But if you enter
select 1 + 'bob'
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'bob' to a column of data type
int.
It shouts "WRONG" at you, just as if you did:
select cast('bob' as int)
So be careful with this sort of operaton, because when you cast a varchar to
an int it will not just return NULL, it gives an error.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>|||In addition to the other posts, check out "data type precedence" in Books On
line. Will probably
explain a lot for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JT" <jt@.nospam.com> wrote in message news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...arkred">
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>