Hi, krickles-ga:
Let's start with a stored procedure that inserts a bunch of records
into a big "lookup" table, as this affords an opportunity to set the
stage by creating the tables we will need to demonstrate the
principles, and also to describe the basics one needs to know to work
with stored procedures.
I'm going to assume you are using the Query Analyzer tool for SQL
Server 2000 and have a reasonable familiarity with it. If I ask you
to do something that doesn't make sense, please let me know; I'll be
happy to clarify.
Here are the creation statements for three tables, a very simplified
version of your add_project table (focusing just on the two columns
that are relevant), a table user_range as I described before to hold
the parameters for which user will own which range of record numbers,
and finally table big_lookup (the target of this initial exercise).
CREATE TABLE add_project (
record_number integer not Null,
inactive bit Null
)
go
CREATE TABLE user_range (
usersid char(10) not Null,
min_num integer not Null,
max_num integer not Null
)
go
CREATE TABLE big_lookup (
usersid char(10) not Null,
rec_num integer not Null
)
go
ALTER TABLE user_range ADD PRIMARY KEY (usersid)
go
INSERT into user_range VALUES ('Susie', 100, 199)
INSERT into user_range VALUES ('Greg', 200, 299)
The above SQL statements set the stage for us to talk about writing a
stored procedure that can churn out a bunch of lookup records,
inserting them into table big_lookup without a lot of tedious typing.
First I'll give you a stored procedure build_lookup that does some
inserting based on some explicit parameters. You'll note that I being
the scripting with a statement to DROP the existing stored procedure
(if any). This is a good programming practice when working with
stored procedure scripting. Of course, the first time we do this the
database will tell us it cannot drop the stored procedure, because it
doesn't exist! But this doesn't hurt to try, in any case.
DROP PROC build_lookup
go
CREATE PROC build_lookup
@usersid char(10),
@min_num integer,
@max_num integer
AS
BEGIN
SET NOCOUNT ON
DECLARE @I INTEGER
SELECT @I = @min_num
WHILE @I <= @max_num
BEGIN
INSERT into big_lookup VALUES (@usersid,@I)
SELECT @I = @I + 1
END
SET NOCOUNT OFF
END
Before we discuss what the above stored procedure does and how, let's
first test it out. You should execute these next three statements one
at a time (you can select one (highlight it) in the Query Analyzer
window where you are trying this out, and hit Ctrl-E (for execute).
build_lookup 'Jane', 0, 99
select * from big_lookup
truncate table big_lookup
The first of these three test statements calls the stored procedure
and passes in the three values as arguments, in corresponding order.
The second statement then shows us that we have successfully inserted
100 records into the table. Finally the last statement (truncate) is
a convenient way to delete everything in the table big_lookup, so we
go back to having a clean slate there.
So let's talk about how the stored procedure build_lookup does this
work. The first thing you notice about the CREATE PROC or CREATE
PROCEDURE code is that it defines the parameters of the stored
procedure right after the name of the stored procedure. Each
parameter is assigned a datatype, and they are separated by commas,
but the list of parameters is not surrounded by parentheses as one
might have expected (say the way column names to a CREATE TABLE script
would be). The word AS separates the procedure name and list of
parameters from the body of the stored procedure.
Although a stored procedure may consist of a single SQL statement,
more often than not it will involve multiple statements, as it does
here. We need the "BEGIN...END" pair of statements to form a "block"
of SQL statements that can be parsed & executed together rather than
separately.
The first statement inside the main block is SET NOCOUNT ON. You
might not have seen this sort of statement before, but it tidies up
the appearance of the the stored procedure's output by suppressing all
the "1 row(s) inserted" messages that we would otherwise get. You
might want to experiment with commenting this line out (and
re-creating the stored procedure) just to see the difference.
The main point of this stored procedure is the looping over an integer
variable @I (which we had to "declare" in order to use as a local
variable within the stored procedure). The Transact-SQL programming
language is in many ways very limited, but the WHILE loop will be
adequate for our needs. Notice that @I is "initialized" before the
loop begins by using a SELECT statement to assign to it the minimum
value. Thereafter the loop is responsible for incrementing @I until
it is no longer within the specified range (less than or equal to the
@max_num paramter), at which point the WHILE statement passes control
to the first exectuable statement following the inner "BEGIN...END"
block that defines the body of the WHILE loop.
In this case that happens to be the SET NOCOUNT OFF statement,
restoring the default behaviour of Query Analyzer right before we exit
the stored procedure entirely (because the final END statement marking
the completion of the stored procedure's body).
Now stored procedures can be building blocks for other stored
procedures. Our next example will illustrate this, and also
introduces a very powerful feature of many stored procedures, the
cursor.
A cursor is essentially a pointer into a set of records returned by a
query. Here I've written a stored procedure build_user_lookup so that
it will consult the records in the user_range table for us, and pass
those corresponding fields to the "building block" stored procedure
build_lookup that we've already written and tested above:
DROP PROC build_user_lookup
CREATE PROC build_user_lookup
AS
BEGIN
SET NOCOUNT ON
DECLARE range_cursor CURSOR
FOR
SELECT usersid, min_num, max_num
FROM user_range
OPEN range_cursor
DECLARE @myusrid char(10)
DECLARE @min_num integer, @max_num integer
-- perform the first FETCH
FETCH NEXT from range_cursor
INTO @myusrid, @min_num, @max_num
-- loop as long as fetch is successful
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE build_lookup @myusrid, @min_num, @max_num
FETCH NEXT from range_cursor
INTO @myusrid, @min_num, @max_num
END
SET NOCOUNT OFF
END
You can test that this stored procedure does the right thing with
these statements (in a Query Analyzer window):
build_user_lookup
select * from big_lookup
truncate table big_lookup
Looking at the text of the build_user_lookup stored procedure, you
will notice that it has no parameters. It doesn't need them because
it is written simply to perform a global insertion of lookup records
for all the users identified in the user_range table.
Within the body of this stored procedure, we have the usual "wrapping"
of the executable statements by SET NOCOUNT ON... SET NOCOUNT OFF.
But immediately within that bracketing pair of statements we declare a
cursor range_cursor.
This declare statement is a little more complex than the declare
statements that are used for defining local variables. It needs to be
because we are associating a SQL query with the cursor, so that when
the cursor is "OPEN"ed later, it actually means to run the SQL query
and make the resulting recordset available through commands like
"FETCH NEXT ... ".
To make a long story short, what we do with the cursor is to perform
an initial FETCH NEXT to get the first record, then go into a WHILE
loop (which checks the fetch status variable pre-defined by the system
to see whether there actually is a next record returned). Here we
process that record simply by passing the three arguments retrieved by
the underlying SQL query against user_range, as arguments to our
"building block" stored procedure build_lookup. So long as the cursor
continues to supply more records, the looping continues. When there
are no more records, the fetch status will become -1 and we exit the
loop.
Please review the sample tables and stored procedures which I've
provided above, and let me know if you have questions or concerns
about them before we dive into the procedures that will answer your
original question (about find the first available or all record
numbers available to a given user).
regards, mathtalk |
Clarification of Answer by
mathtalk-ga
on
16 Apr 2003 11:08 PDT
Hi, krickles-ga:
Thanks for the comment letting me know where you are. Having laid the
groundwork of table definitions, etc., we can proceed to solving your
original question.
If the big_lookup table were present and fully populated, then a
single SQL query (dependent on a parameter @USERSID which identifies
the user) could be used to give the entire set of "available" record
numbers:
SELECT rec_num FROM big_lookup
WHERE usersid = @USERSID
AND NOT EXISTS (SELECT * FROM add_project
WHERE record_number = rec_num
AND inactive = 0
)
A slight modification then gives us the smallest available record
number:
SELECT min(rec_num) FROM big_lookup
WHERE usersid = @USERSID
AND NOT EXISTS (SELECT * FROM add_project
WHERE record_number = rec_num
AND inactive = 0
)
In order to dispense with the big_lookup table we need an alternative
way to "generate" the record numbers which _don't_ exist in the
add_project table. We first show how to do this with a stored
procedure to find the smallest available record number:
CREATE PROCEDURE get1stAvailableRecNumber
@USERSID char(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @REC_NUM INTEGER
DECLARE @MAX_NUM INTEGER
SELECT @REC_NUM = min_num, @MAX_NUM = max_num
FROM user_range
WHERE usersid = @USERSID
WHILE @REC_NUM <= @MAX_NUM AND
EXISTS (SELECT * FROM add_project
WHERE record_number = @REC_NUM
AND inactive = 0
)
BEGIN
SELECT @REC_NUM = @REC_NUM + 1
END
IF @REC_NUM > @MAX_NUM
SELECT @REC_NUM = 0
SELECT @REC_NUM AS record_number
SET NOCOUNT OFF
END
When we want return the entire set of available record numbers for a
user, only slight modifications are needed, including the creation of
a temporary table to hold the results:
CREATE PROCEDURE getAllAvailableRecNumber
@USERSID char(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @REC_NUM INT
DECLARE @MAX_NUM INT
SELECT @REC_NUM = min_num, @MAX_NUM = max_num
FROM user_range
WHERE usersid = @USERSID
CREATE TABLE #t(record_number INT)
WHILE @REC_NUM <= @MAX_NUM
BEGIN
IF NOT EXISTS (SELECT * FROM add_project
WHERE record_number = @REC_NUM
AND inactive = 0
)
INSERT into #t VALUES (@REC_NUM)
SELECT @REC_NUM = @REC_NUM + 1
END
SELECT record_number FROM #t
SET NOCOUNT OFF
END
Here the available record numbers are inserted into temporary table #t
as we find them, looping over the user's range. If there are no
available record numbers, then the table would be empty. In any case
the resulting record set is returned by the final SELECT statement.
SQL Server takes care of dropping the temporary table as control
passes out of the scope of the stored procedure where it's created.
As a final piece of code, we look at using a single SQL query to get
the smallest available record number for a user. The logic of this is
obscure enough to pose a maintenance headache, so it's presented
mainly out of intellectual curiousity. I have prepended a couple of
lines so that query following may be tested within a Query Analyzer
window (using the test data inserted previously). All must be
executed together, so hit Ctrl-E without selecting any text (if this
is the only code in the window) or else with just these lines
selected:
DECLARE @USERSID char(10)
SELECT @USERSID = 'Susie'
SELECT CASE
WHEN NOT EXISTS (SELECT * FROM add_project
WHERE record_number = min_num
AND inactive = 0
)
THEN min_num
ELSE
(SELECT min(record_number) + 1 FROM add_project A
WHERE record_number BETWEEN min_num AND (max_num - 1)
AND NOT EXISTS (SELECT * FROM add_project B
WHERE B.record_number = A.record_number + 1
AND B.inactive = 0
)
)
END
FROM user_range
WHERE usersid = @USERSID
Alternatively you could simple replace the parameter @USERSID at
bottom with constant text 'Susie' or 'Greg'.
In closing I take note of confusedfish-ga's suggestion that this
functionality also could be implemented on the ASP side. Certainly a
possibility, but testing would be needed to convince me that such an
approach would be faster, once the need to access the database for the
user's range of record numbers and the list of those record numbers
_not_ available is taken into account.
regards, mathtalk-ga
|
