stored procedure to retrieve value from table.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure to retrieve value from table.

Author

Topic

pelusa
Starting Member

2 Posts

Posted - 2007-11-29 : 18:24:15

I need help to write a stored procedure, or
pseudo code, that looks into a table named numbers_ranges
and retrieves the next unused value.

The table currently has the following data:

Columns are: id, start_range (22), end_range (22), status (1), date_created (datetime), date_updated (datetime)

1 8057777777450780000000 8057777777450780000010 N 2007-11-29 16:28:46.793 2007-11-29 16:28:46.793
2 8057777777450780000011 8057777777450780000021 N 2007-11-29 16:28:56.497 2007-11-29 16:28:56.497
3 8057777777450780000022 8057777777450780000032 N 2007-11-29 16:29:04.637 2007-11-29 16:29:04.637
4 8057777777450780000033 8057777777450780000043 N 2007-11-29 16:29:13.150 2007-11-29 16:29:13.150
5 8057777777450780000044 8057777777450780000054 N 2007-11-29 16:29:28.340 2007-11-29 16:29:28.340

How is determined what range to use?

The next number value will be retrieved from the range
with the lowest value and with a status of 'N'

When a number has been retrieved then the status of 'N'
needs to change to 'Y' which indicates that now that range
is in use.

When a range of numbers has been used completely, then
the status for that row need to be changed to 'X'

There are 3 possible status:

N = not used
Y = in use
X = already used

Every time that a number is retrieve from the current range,
or when the status of a range changes, the date_updated column
needs to be updated with the current date.

I understand what needs to be done, but I am confused in where to begin.
I am aware that to ask for this kind of help is not nice but if someone
could at least provide me with some guidance I will fell less stressed.

Thank you much in advance.

p.s.

please let me know if the explanation above was not clear.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-29 : 19:06:21

How do you know what the next value is? Is it one of the sart or end numbers?

pelusa
Starting Member

2 Posts

Posted - 2007-11-29 : 19:26:55

the current value is stored in a different table. i thin that the procedre nees to retrieve this value first.

since the tables have not been used yet, the value stored is 0.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-29 : 20:04:40

I need to run, but give this a try and see if it helps. You will need to do some more work to get the updates to work.


DECLARE @StartRange INT

SELECT @StartRange = MIN(Start_Range) 
FROM MyTable 
WHERE Status IN ('N', 'Y'))

SELECT *
FROM MyTable
WHERE Start_Range = @StartRange

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources