During my long SQL-carreer I've managed to steer clear of Cursors. But now I have a project at hand where I do not see a feasable way to NOT use a cursor.
From a pool of available numbers (IMSIs) I need to allocate new ones and add them to a batch table. In total there are three tables affected by the following SP. A batch table storing info on the batch, a table that contains the newly allocated IMSIs and a table holding all IMSIs with a state field to indicate that they are free or not. With in the WHILE loop I need to set the state from 'F'(ree) to 'A'llocated and I have no clue how to update the ImsiCursor. So two questions:
Can this be done without a cursor?
How to update the State attribute of the current cursor location?
ALTER PROCEDURE dbo.spCreateBatch
(
@.BatchID AS int
, @.BatchSize AS int
, @.ImsiCount as smallint
, @.PurchaseOrder AS varchar(12)
)
AS
DECLARE @.counter5 AS char(5)
DECLARE @.FreeIMSIs AS BIGINT
SELECT @.FreeIMSIs = COUNT(Imsi) FROM tblImsiPool WHERE ImsiRangeID=1 AND STATE='F'
IF @.FreeIMSIs < @.BatchSize
BEGIN
RAISERROR ('Not enough free IMSIs in range', 16, 1)
RETURN
END
DECLARE ImsiCursor CURSOR LOCAL FORWARD_ONLY FOR SELECT p.Imsi, p.State FROM tblImsiPool p WHERE p.ImsiRangeID=1 AND p.State='F'
SET NOCOUNT ON
BEGIN TRANSACTION CreateBatch
DECLARE @.BatchID5 AS CHAR(5)
SET @.BatchID5 = RTRIM(LTRIM(@.BatchID))
SET @.BatchID5 = REPLICATE('0', 5 - LEN(@.BatchID5)) + RTRIM(@.BatchID5)
INSERT INTO
tblBatch (BatchID, Vendor, Creation, Required, BatchSize, ImsiCount, PurchaseOrder, PIN1Default, PIN2Default, MBPINDefault, State)
VALUES (@.BatchID5, 'GA', DEFAULT, DATEADD(m, 1, GETDATE()), @.BatchSize, @.ImsiCount, @.PurchaseOrder, 'RANDOM', 'RANDOM', 'RANDOM','C')
DECLARE @.counter AS int
DECLARE @.IccIDBase AS VARCHAR(19)
DECLARE @.IccID AS VARCHAR(20)
DECLARE @.Imsi AS NUMERIC(15)
DECLARE @.state AS CHAR(1)
SELECT @.Imsi = MAX(DefaultImsi) FROM tblSim
OPEN ImsiCursor
FETCH NEXT FROM ImsiCursor INTO @.Imsi, @.State
SET @.counter = 0
WHILE( @.counter <= @.BatchSize )
BEGIN
SET @.counter5 = @.counter
SET @.counter5 = REPLICATE('0', 5 - LEN(RTRIM(@.counter5))) + RTRIM(@.counter5)
SET @.IccIDBase = '89234507' + @.BatchID5 + @.counter5
SET @.IccID = RTRIM(@.IccIDBase) + CONVERT( CHAR(1), dbo.luhn( @.IccIDBase ) )
IF LEN(@.IccID) < 20
SET @.IccID = @.IccID + 'F'
INSERT INTO tblSIM(BatchID, Vendor, ICCID, Pin, Puk, Pin2, Puk2, DefaultImsi)
VALUES( @.BatchID5, 'GA', @.IccID, '', '', '', '', @.Imsi)
UPDATE ImsiCursor SET State = 'A' -- does not work
FETCH NEXT FROM ImsiCursor INTO @.Imsi, @.State
SET @.counter = @.counter + 1
END
CLOSE ImsiCursor
DEALLOCATE ImsiCursor
IF @.@.ERROR = 0
COMMIT TRANSACTION CreateBatch
ELSE
ROLLBACK TRANSACTION CreateBatch
RETURN
From what you have said, it seems reasonable that you could do this without cursors, buit it might be tricky.
You would just do an update to set the number of rows you want to allocate to not allocated (then in 2005, you can use the OUTPUT clause to get the rows you changed, or you can lock the rows with a SELECT into your temp table and and XLOCK in a transaction).
Now it looks like this code:
SET @.counter5 = @.counter
SET @.counter5 = REPLICATE('0', 5 - LEN(RTRIM(@.counter5))) + RTRIM(@.counter5)
SET @.IccIDBase = '89234507' + @.BatchID5 + @.counter5
SET @.IccID = RTRIM(@.IccIDBase) + CONVERT( CHAR(1), dbo.luhn( @.IccIDBase ) )
IF LEN(@.IccID) < 20
SET @.IccID = @.IccID + 'F'
INSERT INTO tblSIM(BatchID, Vendor, ICCID, Pin, Puk, Pin2, Puk2, DefaultImsi)
VALUES( @.BatchID5, 'GA', @.IccID, '', '', '', '', @.Imsi)
Is the meat of the query. You could probably translated the variable to calculations in a SELECT query.
It is all too messy to work with for the uneducated. Please post some sample data and minimal tables that just cover the problems you are having and it will be easier to demonstrate.
