Integer Parameter

I have a parameter field with a data type of int. I want the users to be able to select one or 'all' from the dropdown list. Is there a wildcard for a integer datatype?

Dropdown list example:

All Project Ids

1001

1002

1003

1004

In cases like this I will frequently use a NULL parameter to designate 'all'; can that work for you in this case? An example of this would be something like this:

declare @.example table (integerCol integer)
insert into @.example values (1001)
insert into @.example values (1002)
insert into @.example values (1003)
insert into @.example values (1004)

declare @.anIntegerParm integer
set @.anIntegerParm = null
set @.anIntegerParm = 1002

if @.anIntegerParm is null

select * from @.example

else

select * from @.example
where integerCol = @.anIntegerParm

-- When @.anIntegerParm is null:

-- integerCol
-- --
-- 1001
-- 1002
-- 1003
-- 1004

-- When @.anIntegerParm = 1002:

-- integerCol
-- --
-- 1002

select * from @.example
where @.anIntegerParm is null
or @.anIntegerParm is not null
and integerCol = @.anIntegerParm

-- integerCol
-- --
-- 1002

Integer datatype question

I have a column setup using the Int datatype and a length of 4. First: does this mean that the allowable range is from -9999 to 9999? Second: I can't seem to change the 4 to anything else, how can I modify the length :confused:That's the internaly stored length...

Look up datatypes in books online...

int is actually

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).|||no, the 4 indicates the amount of bytes used. It's a fixed size, you can't change it. As specified by BOL, integer values '(...)from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).'. If this won't do use a bigint instead.

EDIT: Brett, I'll quote you on this: Damn...sniped again

integer datatype confusion, signed vs unsigned

Hi Group
Transact SQL defines that int is:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
1 (2,147,483,647). Storage size is 4 bytes.
This implies that only SIGNED integer values are possible with
SQL-Server. I'm aware that from a data conversion point of view this
is no problem in that a singed integer can be interpreted as unsigned
or signed.
Then, there is a (c) datatype definition SQLUINTEGER as well as
SQLINTEGER, so unsigned integers seem to be suported conversion wise.
However, when it comes to sorting or using select, things are IMHO
different in that -1 is smaller than '0' if interpreted as signed, but
obviousely the biggest possible value interpreted as unsigned etc. I
therefore somehow miss the possibility to declare an integer collumn
to be "unsigned" so as sorting etc. is made the right way.
The same problem obviousely exists with small integers except that
their range is limitted to what can be expressed with 16 bits.
Could someone sheed some light on this?
TIA
MarkusThe INT datatype is signed, as are BIGINT and SMALLINT. TINYINT is
the only one not signed. This is can not be changed.
If negative numbers are not valid for a column, enforce that with a
CHECK constraint:
CHECK (IntCol >= 0)
Roy Harvey
Beacon Falls, CT
On Wed, 19 Apr 2006 11:26:57 +0200, Markus Zingg <m.zingg@.nct.ch>
wrote:

>Hi Group
>Transact SQL defines that int is:
>Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
>1 (2,147,483,647). Storage size is 4 bytes.
>This implies that only SIGNED integer values are possible with
>SQL-Server. I'm aware that from a data conversion point of view this
>is no problem in that a singed integer can be interpreted as unsigned
>or signed.
>Then, there is a (c) datatype definition SQLUINTEGER as well as
>SQLINTEGER, so unsigned integers seem to be suported conversion wise.
>However, when it comes to sorting or using select, things are IMHO
>different in that -1 is smaller than '0' if interpreted as signed, but
>obviousely the biggest possible value interpreted as unsigned etc. I
>therefore somehow miss the possibility to declare an integer collumn
>to be "unsigned" so as sorting etc. is made the right way.
>The same problem obviousely exists with small integers except that
>their range is limitted to what can be expressed with 16 bits.
>Could someone sheed some light on this?
>TIA
>Markus|||Markus Zingg (m.zingg@.nct.ch) writes:
> Transact SQL defines that int is:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
> 1 (2,147,483,647). Storage size is 4 bytes.
> This implies that only SIGNED integer values are possible with
> SQL-Server. I'm aware that from a data conversion point of view this
> is no problem in that a singed integer can be interpreted as unsigned
> or signed.
> Then, there is a (c) datatype definition SQLUINTEGER as well as
> SQLINTEGER, so unsigned integers seem to be suported conversion wise.
I'm not really sure where you find this SQLUINTEGER type, but if the
type is in C, I presume that the type collection has been defined for
more engines than SQL Server in mind, and some of those engines may
support an unsigned integer type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx