Replace cursors Using table Variable

I have a sample code that might help you to replace Cursors with table variable.
This one is good if the number of records are not too many(in thousands).
Try implementing the following code.

-- Cursor Simulator
CREATE PROC CursorSimulator
AS
/*Prepare TABLE variable to take resultset*/
DECLARE @tbl TABLE(
RowID INT IDENTITY(1, 1),
CompanyName VARCHAR(100),
ContactName VARCHAR(50))
/*Local variables */
DECLARE @ContName VARCHAR(100),
@CompName VARCHAR(50),
@count int, /*create local @@fetch_status*/
@iRow int /*row pointer (index)*/
/* create array simulator */
INSERT @tbl
SELECT CompanyName, ContactName
FROM Suppliers
WHERE ContactName LIKE 'c%'
/*get array Upper Bound (highest ID number)*/
SET @count = @@ROWCOUNT
/*initialize index counter*/
SET @iRow = 1
/*establish loop structure*/
WHILE @iRow <= @count
BEGIN
/*get row values*/
SELECT @ContName = CompanyName, @CompName = ContactName
FROM @tbl
WHERE RowID = @iRow
/*perform operations with single row*/
PRINT 'My cursor row | ' + @ContName + ' | ' + @CompName
/*go to next row*/
SET @iRow = @iRow + 1
END

Reference :http://www.sqlservercentral.com/columnists/aGrinberg/thearrayinsqlserver2000.asp

This doesn't replace cursors?! You're just copying data from a table into a table variable, and fetching that same data one row at a time.

What's the point?

Adriaan
Well in a scenario like mine does it not replace the cursor .
I was peforming this using a cursor but i have replaced using the table variable does this help in optimization.
Pls advice

DECLARE @count int,@iRow int ,@OLPIN_CONTGROUP int, @OLPIN_CONTCODE CHAR(5),@VENDOR_ID INT
DECLARE @TableVar table (
RowID int identity(1,1),
OLPIN_CONTCODE varchar(25) NOT NULL)

Insert Into @TableVar (OLPIN_CONTCODE)
(
SELECTDISTINCT
A.DICONT AS OLPIN_CONTCODE
FROM
INSP_HDR_INTFC A (nolock)
WHERE
NOT EXISTS (SELECT * FROM VENDOR_OLPIN_CONT VOC (nolock)
WHERE VOC.OLPIN_CONTCODE = A.DICONT)
AND A.PROCESS_ID = @PROCESS_ID
)
SET @count = @@ROWCOUNT
SET @iRow = 1
WHILE @iRow <= @count
Begin
SELECT @OLPIN_CONTCODE = OLPIN_CONTCODE
FROM @TableVar
WHERE RowID = @iRow
BEGIN
SELECT@OLPIN_CONTGROUP = INVEND
FROMVENDOR_OLPIN_MAP (nolock)
WHEREINVNDNBR = @OLPIN_CONTCODE
Begin
INSERT INTO VENDORS
(
OLPIN_CONTCODE,
OLPIN_CONTGROUP
)
VALUES
(
@OLPIN_CONTCODE,
ISNULL(@OLPIN_CONTGROUP,0)
)
SELECT @VENDOR_ID = @@IDENTITY
INSERT INTO VENDOR_OLPIN_CONT
(
VENDOR_ID,
OLPIN_CONTCODE
)
VALUES
(
@VENDOR_ID,
@OLPIN_CONTCODE
)
END
SET @iRow = @iRow + 1
END
END

I doubt if it makes the slightest bit of difference.

Can you not write this as some sort of a GROUP BY query? You can INSERT results from any variation of a SELECT query, including GROUP BY.

This will make the processing set-based, which 9 times out of 10 is faster than using a (fake) cursor.

Adriaan Here is the Cursor which i have replaced with the above code how can i use the set-based processing.Does set based means doing update and using set column fields.

DECLARE CUR_VENDOR CURSOR FOR
SELECTDISTINCT
A.DICONT AS OLPIN_CONTCODE
FROM
INSP_HDR_INTFC A (nolock)
WHERE
NOT EXISTS (SELECT * FROM VENDOR_OLPIN_CONT VOC (nolock)
WHERE VOC.OLPIN_CONTCODE = A.DICONT)
AND A.PROCESS_ID = @PROCESS_ID

OPEN CUR_VENDOR

FETCH NEXT FROM CUR_VENDOR INTO @OLPIN_CONTCODE

IF @@FETCH_STATUS <> 0
BEGIN
SELECT @MSG_TXT = 'VENDORS cursor not executed; continuing'
END

WHILE @@FETCH_STATUS = 0
BEGIN
-- Code block
END

Nobody forces you to program with variables! You can if you must, but you don't have to.

Also, it looks like you have no idea that there is such a thing as a JOIN in queries - look at the basic SELECT syntax in BOL.

In a JOIN, you connect data from two tables, and you can include columns from all JOINed tables in the column list. The parameters that you are reading in the first cursor, which you then pass on for opening the second cursor, are mimicking a JOIN.

If you'll excuse the play on your name - SQL really is much easier than you think!

Guess youre talking about a inner join in the following and not using a corelated query.
but a inner join would bring the ones which exists and not the ones which does not exists.
i am very confused.or maybe i am a ammeture in writing sql and working on making sql easy on me.


SELECT DISTINCT
A.DICONT AS OLPIN_CONTCODE
FROM
INSP_HDR_INTFC A (nolock)
WHERE
NOT EXISTS (SELECT * FROM VENDOR_OLPIN_CONT VOC (nolock)
WHERE VOC.OLPIN_CONTCODE = A.DICONT)
AND A.PROCESS_ID = @PROCESS_ID

You're reading the A.DICONT value here ...

SELECT DISTINCT
A.DICONT AS OLPIN_CONTCODE
FROM
INSP_HDR_INTFC A (nolock)
WHERE
NOT EXISTS (SELECT * FROM VENDOR_OLPIN_CONT VOC (nolock)
WHERE VOC.OLPIN_CONTCODE = A.DICONT)
AND A.PROCESS_ID = @PROCESS_ID

... and put it into a variable @OLPIN_CONTCODE.

You then use the variable to read INVEND and put it into a variable @OLPIN_CONTGROUP:

SELECT @OLPIN_CONTGROUP = INVEND
FROM VENDOR_OLPIN_MAP (nolock)
WHERE INVNDNBR = @OLPIN_CONTCODE

And finally you insert those two values into a table. All this can be done in a single statement:

INSERT INTO ... (......)
SELECT DISTINCT A.DICONT, B.INVEND
FROM INSP_HDR_INTFC A (nolock)
INNER JOIN VENDOR_OLPIN_MAP B (nolock) ON A.DICONT = B.INVNDNBR
WHERE NOT EXISTS
(SELECT * FROM VENDOR_OLPIN_CONT VOC (nolock)
WHERE VOC.OLPIN_CONTCODE = A.DICONT)
AND A.PROCESS_ID = @PROCESS_ID

If you're looping through a cursor to read @PROCESS_ID, and repeat the above, then think again.

I will try the above query and implement it.
I think what u are saying would also optimize my code efficiently.
but i did not get youre last sentence "
If you're looping through a cursor to read @PROCESS_ID, and repeat the above, then think again"
means to implement or not to implement?
Process_id is one but the id's corresponding to process id are multiple the reason for using cursor.
thanks

So you are reading @PROCESS_ID from another table? Add that table with a JOIN, just like we did the other two tables, and in case you're filtering that new table, then add the criteria to the WHERE statement.

Also, don't believe each article on the net. If there is an discussion about the article it is good idea to read it before blindly apply recommendations from there.

... and even more important, test everything yourself first!