t-sql dynamic cursors

Question

hi.

May I use variable to declaring cursors??

I want to creating dynamic cursor, how can i do this??

Regards

I have table: CREATE TABLE [UsersUniTask] ( [id] uniqueidentifier CONSTRAINT [DF_UsersUniTask_id] DEFAULT newid() NOT NULL, [userUniID] uniqueidentifier NOT NULL, [taskID] int NOT NULL, [time] datetime NOT NULL, [doTask] int NOT NULL, [priority] int NOT NULL, CONSTRAINT [PK_UsersUniTask] PRIMARY KEY CLUSTERED ([id]), CONSTRAINT [FK_UsersUniTask_UsersUni] FOREIGN KEY ([userUniID]) REFERENCES [UsersUni] ([id]) ON UPDATE NO ACTION ON DELETE CASCADE ) ON [PRIMARY] GO

Answer 1

Can you explain more about what you mean? If you are talking about declaring the cursor in a dynamic context as in the following example, then yes you can:

DECLARE @i int -- variable input
DECLARE @valuableData int

SET @i = 1 -- value for that input, this could be set by a query

DECLARE cursorFoo CURSOR FOR
    SELECT valuableData
    FROM myTable
    WHERE someParameter = @i

OPEN cursorFoo 

WHILE (1=1)
BEGIN
    FETCH NEXT FROM cursorFoo 
         INTO @valuableData

    IF (@@FETCH_STATUS <> 0) BREAK

    SELECT @valuableData -- Do something with your data
END

CLOSE cursorFoo

EDIT due to discussion in comments

You should have two separate program loops here

Loop 1:

1. Webservice adds tasks to permanent table with information on priority.

Loop 2:

1. Server script queries permanent table for most important task
2. Server script removes task from table and processes task (or hands that information off to a script that does the work
3. Server script goes back to permanent table and looks for most important task

SQL is meant to store data, not loop around and process it. The processing should be done with a server script. That script should get the data from the database. You start to have the concurrency issues you have having right now when SQL is writing and reading and looping through the same temporary table concurrently. You can do processing in SQL, but you should only use temp tables for data that is relevant only to that particular process.