Convert Problem

Hello,

I have a stored procedure in SQL Server 2000 and I am trying to do something like this for example:
declare @.s varchar(50), @.i smallint

if isnumeric(@.s) = 1
begin
select @.i = convert(smallint, @.s)
if @.@.error <> 0
print 'error'
end
else
print 'No error'

The problem is what can I do if the value of @.s is '12.35'? This generates the 'Syntax error converting the varchar value to a column of data type smallint.' because the numeric value is actually decimal and the error is not caught by the @.@.error check.
The only alternative I can think of is to convert @.s to decimal the convert again but this is not suitable as this is for validation purposes and if the value is decimal I need to identify it and ignore it.

Is there any way to check for or handle this scenario?

Thanks for any help.
ACWhy are you checking to see if a VARCHAR(50) field contains a number?
But hey, I'm sure you know what you're doing...

if isnumeric(@.s) = 1
begin
select @.i = convert(smallint, @.s)
end
if @.@.error <> 0
print 'error'
end
else begin
print 'No error'
end|||declare @.s varchar(50), @.i smallint

set @.s = '12.35'

if isnumeric(@.s) = 1
begin
select @.i = floor(@.s) -- round(@.s, 10, 0)
if @.@.error <> 0
print 'error'
end
else
print 'No error'

select @.i|||Please note that the differences in the statement set up.

I have separated the Isnumeric into it's own If.|||Thanks for the input Peso.
However, this is not what I want to do. For validation purposes I want to catch this value if it is invalid and ignore it, not attempt to convert it to a valid value.|||Thanks georgev but this doesn't make a difference.
The error returns before the process gets to checking @.@.error.

Please note that the differences in the statement set up.

I have separated the Isnumeric into it's own If.|||I'd suggest using:DROP FUNCTION dbo.IsInt
GO
-- ptp 20070913 Test to see if a string could be an MS-SQL integer

CREATE FUNCTION dbo.IsInt(
@.pcArg NVARCHAR(50)
)
RETURNS INT
AS BEGIN
DECLARE
@.result INT
, @.work BIGINT

SET @.pcArg = Rtrim(LTrim(@.pcArg))
IF @.pcArg LIKE '%[^-+0-9]%' SET @.result = 0 -- Invalid character(s)
ELSE IF 22 < DataLength(@.pcArg) SET @.result = 0 -- Impossibly long
ELSE IF @.pcArg LIKE '[0-9]' SET @.result = 1 -- Singleton digit
ELSE IF @.pcArg LIKE '[-+]%[-+]%' SET @.result = 0 -- Multiple signs
ELSE
BEGIN -- Clean input
SET @.work = Convert(BIGINT, @.pcArg)
IF @.work BETWEEN -2147483648 AND 2147483647
SET @.result = 1 -- Passed range check
ELSE
SET @.result = 0 -- Out of range
END

RETURN @.result
END
GO

SELECT c, dbo.IsInt(c)
FROM (SELECT '99999999999999999999' AS c
UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
UNION ALL SELECT '2147483647' UNION ALL SELECT '2147483648'
UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
) AS zThis probably don't perform fantastically, but it ought to get the job done and I don't know of any input that will break it.

-PatP|||Thanks Pat, I should be able to use some pattern matching.

I'd suggest using:DROP FUNCTION dbo.IsInt
GO
-- ptp 20070913 Test to see if a string could be an MS-SQL integer

CREATE FUNCTION dbo.IsInt(
@.pcArg NVARCHAR(50)
)
RETURNS INT
AS BEGIN
DECLARE
@.result INT
, @.work BIGINT

SET @.pcArg = Rtrim(LTrim(@.pcArg))
IF @.pcArg LIKE '%[^-+0-9]%' SET @.result = 0 -- Invalid character(s)
ELSE IF 22 < DataLength(@.pcArg) SET @.result = 0 -- Impossibly long
ELSE IF @.pcArg LIKE '[0-9]' SET @.result = 1 -- Singleton digit
ELSE IF @.pcArg LIKE '[-+]%[-+]%' SET @.result = 0 -- Multiple signs
ELSE
BEGIN -- Clean input
SET @.work = Convert(BIGINT, @.pcArg)
IF @.work BETWEEN -2147483648 AND 2147483647
SET @.result = 1 -- Passed range check
ELSE
SET @.result = 0 -- Out of range
END

RETURN @.result
END
GO

SELECT c, dbo.IsInt(c)
FROM (SELECT '99999999999999999999' AS c
UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
UNION ALL SELECT '2147483647' UNION ALL SELECT '2147483648'
UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
) AS zThis probably don't perform fantastically, but it ought to get the job done and I don't know of any input that will break it.

-PatP|||I was surprised by your response, then realized that I'd written the function for INT and you wanted SMALLINT. You only need to change one line to:IF @.work BETWEEN -32768 AND 32767and you're "good to go" for SMALLINT testing.

Sorry about that oversight!

-PatP|||I think that I've found a better answer. This function checks a string argument to see if it can be an MS-SQL integer value, and it returns a string with a character for each data type that the argument could be. This won't win any speed prizes, but it avoids at least two of the pitfalls that I found in the prior function and it provides more functionality too.DROP FUNCTION dbo.IntegerTypes
GO
-- ptp 20070913 Return which types of MS-SQL integer the argument could be

CREATE FUNCTION dbo.IntegerTypes(
@.pcArg NVARCHAR(39)
)
RETURNS VARCHAR(8)
AS BEGIN
DECLARE
@.result VARCHAR(8)
, @.work NUMERIC(38)

SET @.pcArg = Rtrim(LTrim(@.pcArg))
IF @.pcArg LIKE '%[^-+0-9]%' SET @.result = '' -- Invalid character(s)
ELSE IF @.pcArg NOT LIKE '%[0-9]%' SET @.result = '' -- No digits
ELSE IF @.pcArg LIKE '[-+0-9]%[-+]%' SET @.result = '' -- Sign after sign or digit
ELSE IF 78 < DataLength(@.pcArg) SET @.result = '' -- Impossibly long
ELSE IF 78 = DataLength(@.pcArg) AND @.pcArg NOT LIKE '[-+]%' SET @.result = ''
ELSE
BEGIN -- Clean input
SET @.result = 'N'
SET @.work = @.pcArg
IF @.work BETWEEN -9223372036854775808 AND 9223372036854775807 SET @.result = @.result + 'B' -- BIGINT
IF @.work BETWEEN -2147483648 AND 2147483647 SET @.result = @.result + 'I' -- INT
IF @.work BETWEEN -32768 AND 32767 SET @.result = @.result + 'S' -- SMALLINT
IF @.work BETWEEN 0 AND 255 SET @.result = @.result + 'T' -- TINYINT
END

RETURN @.result
END
GO

SELECT c, dbo.IntegerTypes(c)
FROM (SELECT '9999999999999999999999999999999999999999' AS c
UNION ALL SELECT '99999999999999999999999999999999999999'
UNION ALL SELECT '+99999999999999999999999999999999999999'
UNION ALL SELECT '-99999999999999999999999999999999999999'
UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
UNION ALL SELECT '2147483647' UNION ALL SELECT '2147483648'
UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
) AS z-PatP|||WHERE Col1 NOT LIKE '%[^0-9]%'|||WHERE Col1 NOT LIKE '%[^0-9]%'SELECT Replicate('9', 99)-PatP