locks in a stored procedure

Are locks necessary in a stored procedure?
I have a short stored procedure where I want to update n records to indicate
their intended use in a non-database procedure. I want to select these
records based on the priority and scheduled process date, and according to a
parameter in the procedure. This is what I see as optimal...
----
CREATE PROCEDURE dbo.GetBatchForProcessing
@.thread_guid uniqueidentifier = Null OUTPUT,
@.batch_count int
AS
SET @.thread_guid = ISNULL(@.thread_guid, NewID())
UPDATE Queue SET lockID = @.thread_guid
WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
process_date IS NULL AND lockID IS NULL AND scheduled_date <= getdate()
ORDER BY priority, scheduled_date)
SELECT * FROM Queue WHERE lockID = @.thread_guid
----
...but I cannot do this because the @.batch_count parameter cannot be used
dynamically in the TOP statement, and just to say up front I am REALLY
against using dynamic SQL in stored procedures. I think it obviates the
point of using the procedure in the first place.
Since the limit on the rows I want affected in the UPDATE are in a subquery,
using SET ROWCOUNT @.batch_count won't work. Even if it did, SQL Server
won't allow the ORDER BY clause in the subquery without the TOP statement.
Which leaves me with using SET ROWCOUNT to select the set of queueID's into
a temp table, and then perform the update clause to match against what I've
put into the temp table. Now, while doing this shouldn't cause any problems
with the way my application works, design-wise it bothers me having that
step between creating the temp table and doing the update to the Queue
table. How can I temporarily lock the Queue table for these two steps?Random,
TOP @.Variable works fine in SQL Server 2000. What version are you running?
Or, did you upgrade to 2000, but your database remains in 70 mode? In that
case use sp_dbcmptlevel to set it to 80.
RLF
"Random" <cipherlad@.hotmail.com> wrote in message
news:eQHDkjlKFHA.4032@.TK2MSFTNGP14.phx.gbl...
> Are locks necessary in a stored procedure?
> I have a short stored procedure where I want to update n records to
> indicate their intended use in a non-database procedure. I want to select
> these records based on the priority and scheduled process date, and
> according to a parameter in the procedure. This is what I see as
> optimal...
> ----
> CREATE PROCEDURE dbo.GetBatchForProcessing
> @.thread_guid uniqueidentifier = Null OUTPUT,
> @.batch_count int
> AS
> SET @.thread_guid = ISNULL(@.thread_guid, NewID())
> UPDATE Queue SET lockID = @.thread_guid
> WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
> process_date IS NULL AND lockID IS NULL AND scheduled_date <= getdate()
> ORDER BY priority, scheduled_date)
> SELECT * FROM Queue WHERE lockID = @.thread_guid
> ----
> ...but I cannot do this because the @.batch_count parameter cannot be used
> dynamically in the TOP statement, and just to say up front I am REALLY
> against using dynamic SQL in stored procedures. I think it obviates the
> point of using the procedure in the first place.
> Since the limit on the rows I want affected in the UPDATE are in a
> subquery, using SET ROWCOUNT @.batch_count won't work. Even if it did, SQL
> Server won't allow the ORDER BY clause in the subquery without the TOP
> statement.
> Which leaves me with using SET ROWCOUNT to select the set of queueID's
> into a temp table, and then perform the update clause to match against
> what I've put into the temp table. Now, while doing this shouldn't cause
> any problems with the way my application works, design-wise it bothers me
> having that step between creating the temp table and doing the update to
> the Queue table. How can I temporarily lock the Queue table for these two
> steps?
>|||Random wrote:
> Are locks necessary in a stored procedure?
> I have a short stored procedure where I want to update n records to
> indicate their intended use in a non-database procedure. I want to
> select these records based on the priority and scheduled process
> date, and according to a parameter in the procedure. This is what I
> see as optimal...
> ----
> CREATE PROCEDURE dbo.GetBatchForProcessing
> @.thread_guid uniqueidentifier = Null OUTPUT,
> @.batch_count int
> AS
> SET @.thread_guid = ISNULL(@.thread_guid, NewID())
> UPDATE Queue SET lockID = @.thread_guid
> WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
> process_date IS NULL AND lockID IS NULL AND scheduled_date <=
> getdate() ORDER BY priority, scheduled_date)
> SELECT * FROM Queue WHERE lockID = @.thread_guid
> ----
> ...but I cannot do this because the @.batch_count parameter cannot be
> used dynamically in the TOP statement, and just to say up front I am
> REALLY against using dynamic SQL in stored procedures. I think it
> obviates the point of using the procedure in the first place.
> Since the limit on the rows I want affected in the UPDATE are in a
> subquery, using SET ROWCOUNT @.batch_count won't work. Even if it
> did, SQL Server won't allow the ORDER BY clause in the subquery
> without the TOP statement.
> Which leaves me with using SET ROWCOUNT to select the set of
> queueID's into a temp table, and then perform the update clause to
> match against what I've put into the temp table. Now, while doing
> this shouldn't cause any problems with the way my application works,
> design-wise it bothers me having that step between creating the temp
> table and doing the update to the Queue table. How can I temporarily
> lock the Queue table for these two steps?
If you want to lock the entire table you can use a TABLOCKX hint on the
query that retrieves the data for the temp table.
David Gugick
Imceda Software
www.imceda.com|||You could try using a table variable with rowcount.. Here's an example I
created using my data...
DECLARE @.varInt INT
SET @.varInt = 3
SET ROWCOUNT @.varInt
DECLARE @.table TABLE(BrochureModelID INT)
INSERT INTO @.table SELECT
BrochureModelID
FROM BrochureModel
UPDATE BrochureModel SET
CreatedBy = 'someone'
WHERE BrochureModelID IN (SELECT BrochureModelID FROM @.table)
SET ROWCOUNT 0
--update here...
This seemed to work..
"Random" wrote:

> Are locks necessary in a stored procedure?
> I have a short stored procedure where I want to update n records to indica
te
> their intended use in a non-database procedure. I want to select these
> records based on the priority and scheduled process date, and according to
a
> parameter in the procedure. This is what I see as optimal...
> ----
> CREATE PROCEDURE dbo.GetBatchForProcessing
> @.thread_guid uniqueidentifier = Null OUTPUT,
> @.batch_count int
> AS
> SET @.thread_guid = ISNULL(@.thread_guid, NewID())
> UPDATE Queue SET lockID = @.thread_guid
> WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
> process_date IS NULL AND lockID IS NULL AND scheduled_date <= getdate()
> ORDER BY priority, scheduled_date)
> SELECT * FROM Queue WHERE lockID = @.thread_guid
> ----
> ...but I cannot do this because the @.batch_count parameter cannot be used
> dynamically in the TOP statement, and just to say up front I am REALLY
> against using dynamic SQL in stored procedures. I think it obviates the
> point of using the procedure in the first place.
> Since the limit on the rows I want affected in the UPDATE are in a subquer
y,
> using SET ROWCOUNT @.batch_count won't work. Even if it did, SQL Server
> won't allow the ORDER BY clause in the subquery without the TOP statement.
> Which leaves me with using SET ROWCOUNT to select the set of queueID's int
o
> a temp table, and then perform the update clause to match against what I'v
e
> put into the temp table. Now, while doing this shouldn't cause any proble
ms
> with the way my application works, design-wise it bothers me having that
> step between creating the temp table and doing the update to the Queue
> table. How can I temporarily lock the Queue table for these two steps?
>
>|||I'm running SQL Server 2000. I ran the sp_dbcmptlevel procedure to ensure I
was running compatibility level correctly, but that didn't change the TOP
restriction. I'd read in an earlier message in this group that that feature
wasn't going to be available unitl SQL Server 2005.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:eX9huvlKFHA.3076@.tk2msftngp13.phx.gbl...
> Random,
> TOP @.Variable works fine in SQL Server 2000. What version are you
> running? Or, did you upgrade to 2000, but your database remains in 70
> mode? In that case use sp_dbcmptlevel to set it to 80.
> RLF
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:eQHDkjlKFHA.4032@.TK2MSFTNGP14.phx.gbl...
>