Getting the maximum value of a datatype

726 views
Skip to first unread message

Caitlin

unread,
Apr 24, 2007, 5:55:05 PM4/24/07
to
Is there any system function in SQL Server 2005 that would return the
maximum possible value of an int? (I know that it's 2147483647; I'm
looking for a way to avoid pasting in that number.)

(What I'm trying to do, by the way, is run a comparison between two
positive integer values that might be null so that I only get a false
when they're both non-null and the first is smaller than the second.
"IF COALESCE(@FirstInt, 2147483647) < COALESCE(@SecondInt, 0)" does
what I want, but it's hardly intuitive to type or maintain. Something
like "COALESCE(@FirstInt, MAXVALUE(int)" would be a lot clearer....)

Thanks,
Caitlin Shaw

TheSQLGuru

unread,
Apr 24, 2007, 6:54:25 PM4/24/07
to
How about this for complexity :))

declare @firstint int, @secondint int

--testing both null
select
CASE WHEN @FirstInt IS NOT NULL AND @SecondInt IS NOT NULL THEN
CASE WHEN @FirstInt < @SecondInt THEN 1 ELSE 0 END
ELSE 0 END

--testing one null value
set @firstint = 1
select
CASE WHEN @FirstInt IS NOT NULL AND @SecondInt IS NOT NULL THEN
CASE WHEN @FirstInt < @SecondInt THEN 1 ELSE 0 END
ELSE 0 END

--testing first < second
set @secondint = 2
select
CASE WHEN @FirstInt IS NOT NULL AND @SecondInt IS NOT NULL THEN
CASE WHEN @FirstInt < @SecondInt THEN 1 ELSE 0 END
ELSE 0 END

--testing first > second
set @secondint = -2
select
CASE WHEN @FirstInt IS NOT NULL AND @SecondInt IS NOT NULL THEN
CASE WHEN @FirstInt < @SecondInt THEN 1 ELSE 0 END
ELSE 0 END


To answer your original question, I don't know of a place (that can be
accessed) that specifies the maxium values for various numeric datatypes.
But you could pretty easily construct your own user function that takes a
datatype as an input and outputs the known upper limit value for that type
with a simple case statement. Just wrap the following snippet with create
function code:


declare @datatype varchar(20)
set @datatype = 'int'

select case when @datatype = 'int' then 2147483647
when @datatype = 'smallint' then 32767
when @datatype = 'tinyint' then 255
when @datatype = 'bigint' then 9223372036854775807
end

--
TheSQLGuru
President
Indicium Resources, Inc.

"Caitlin" <cs...@collegeboard.com> wrote in message
news:1177451704.9...@u32g2000prd.googlegroups.com...

Gert-Jan Strik

unread,
Apr 25, 2007, 3:55:07 PM4/25/07
to
No, there isn't.

Maybe you prefer some window dressing, such as:

IF COALESCE(@FirstInt, 0x7FFFFFFF) < COALESCE(@SecondInt, 0x80000000)


However, a cleaner solution is to use a denormalized constants table:

CREATE TABLE datatype_extrema
(min_bit bit NOT NULL DEFAULT (0) CHECK (min_Bit=0)
,max_bit AS CAST(0x1 AS bit)
,min_tinyint AS CAST(0x00 AS tinyint)
,max_tinyint AS CAST(0xFF AS tinyint)
,min_smallint AS CAST(0x8000 AS smallint)
,max_smallint AS CAST(0x7FFF AS smallint)
,min_int AS CAST(0x80000000 AS int)
,max_int AS CAST(0x7FFFFFFF AS int)
,min_bigint AS CAST(0x8000000000000000 AS bigint)
,max_bigint AS CAST(0x7FFFFFFFFFFFFFFF AS bigint)
,min_smalldatetime AS CAST('19000101 00:00' AS smalldatetime)
,max_smalldatetime AS CAST('20790606 23:59' AS smalldatetime)
,min_datetime AS CAST('17530101 00:00:00.000' AS datetime)
,max_datetime AS CAST('99991231 23:59:59.997' AS datetime)
)
INSERT INTO datatype_extrema DEFAULT VALUES
GO
CREATE TRIGGER nochange_datatype_extrema
ON datatype_extrema INSTEAD OF INSERT, UPDATE, DELETE
AS BEGIN
RAISERROR ('No changes allowed for table datatype_extrema.', 16, 1)
ROLLBACK TRANSACTION
END
GO

After that, you can either copy a maximum value to a local variable or
(when using queries) cross join with this table.

Declare @max_int int
Set @max_int=(SELECT max_int FROM datatype_extrema)
IF COALESCE(@FirstInt, @max_int) < COALESCE(@SecondInt, 0)

HTH,
Gert-Jan

Caitlin

unread,
Apr 30, 2007, 5:03:31 PM4/30/07
to
> To answer your original question, I don't know of a place (that can be
> accessed) that specifies the maxium values for various numeric datatypes.

Alas! But thanks for the answer.

As for the complexity, the most common statement needing to use this
comparison is actually this:

IF (SELECT COALESCE(Progression, 2147483647)
FROM tbSDST_StudentStatus WHERE PKID = @NewSDST_PKID)
< (SELECT COALESCE(Progression, 0)
FROM tbSDST_StudentStatus WHERE PKID = @OldSDST_PKID)

...so a CASE statement would be more trouble than its readability
would be worth, I feel. I might go for the user-defined function....

--Caitlin Shaw

Caitlin

unread,
Apr 30, 2007, 5:04:49 PM4/30/07
to
Thanks for answering! I hadn't thought of using a static table; that
might come in handy.

--Caitlin Shaw

> >CaitlinShaw- Hide quoted text -
>
> - Show quoted text -


Reply all
Reply to author
Forward
0 new messages