General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Title:
Implementing a thread-safe queue (stack) in SQL Server 2000
I received this question from a developer I work with who is used to working with sybase... I really don't know what he means, I beleive he's referrring to the use of transactions and not to the use of actual "Windows Thread".
Here is the question...
I've got a situation where I need to implement a thread-safe queue (stack) in SQL Server 2000. I've done this in Sybase's T-SQL, but was wondering if there are any MS SQL specific functions or commands that make this easier.
Sybase T-SQL...
-------------------
if exists(select * from sysobjects where type = 'P' and name = "sp_q_start")
drop procedure sp_q_start
go
create procedure sp_q_start(
@max_cnt int = 1,
) as
begin
declare @begin_dttm datetime
select @begin_dttm = getdate()
begin tran queue_start
/* limit the number of rows updated */
set rowcount @max_cnt
/* mark next available rows from q as started;
use spid to indicate which process started them */
update q set
status = 1,
begin_dttm = @begin_dttm,
import_id = @@spid
where status = 0 and filetype_cd <= 40
if @@error != 0
begin
rollback
return 1
end
commit
/* get result set */
select seq_id, file_nm, office_id, filetype_cd, retry_cnt, thread_id from q
where status = 1 and import_id = @@spid and begin_dttm = @begin_dttm
return 0
end
go
-------------------
Thanks
Bob Horkay
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only