sql statement

[harish]

I am having a database table which has a column of datatype number.
i am having a function which takes a number as the parameter and
returns a number which is the next
highest number in the database than the passed parameter.

suppose if i my database is having the data in a column as below

1
2
3
4
5

If a pass 3 to my function, i should get 4 as the return value.
If a pass 4 to my function, i should get 5 as the return value.

Can anybody write a sql statement to do this operation. I am using sql
server, but i want the query to get executed on any database.

[John Gilson]

Re: sql statement

"harish" <harishksh@yaho o.com> wrote in message
news:ff46df6e.0 312230530.39642 d81@posting.goo gle.com...[color=blue]
> I am having a database table which has a column of datatype number.
> i am having a function which takes a number as the parameter and
> returns a number which is the next
> highest number in the database than the passed parameter.
>
> suppose if i my database is having the data in a column as below
>
> 1
> 2
> 3
> 4
> 5
>
> If a pass 3 to my function, i should get 4 as the return value.
> If a pass 4 to my function, i should get 5 as the return value.
>
> Can anybody write a sql statement to do this operation. I am using sql
> server, but i want the query to get executed on any database.[/color]

CREATE TABLE Numbers
(
n INT NOT NULL PRIMARY KEY
)

INSERT INTO Numbers (n)
VALUES (1)
INSERT INTO Numbers (n)
VALUES (2)
INSERT INTO Numbers (n)
VALUES (3)
INSERT INTO Numbers (n)
VALUES (4)
INSERT INTO Numbers (n)
VALUES (5)

CREATE FUNCTION NextNumber (@n INT)
RETURNS INT
AS
BEGIN
RETURN(
SELECT MIN(n)
FROM Numbers
WHERE n > @n
)
END

SELECT dbo.NextNumber( 3)

4

Regards,
jag