Temporary table in SQL Server to avoid cursor

Question

I have written the following code in a SQL Server stored procedure to avoid cursor in the code. I have used temporary table for storing data and loop through the data.

SELECT * 
INTO #temp
FROM 
    (SELECT * 
     FROM -----------------) AS x

SET @iRwCnt = @@ROWCOUNT 

CREATE CLUSTERED INDEX idx_tmp ON #temp(ACID) WITH FILLFACTOR = 100

WHILE @iRwCnt > 0
BEGIN
    SELECT TOP 1 @sValue = ACID 
    FROM #temp

    SET @iRwCnt = @@ROWCOUNT 

    IF @iRwCnt > 0
    BEGIN
        SELECT 
            @mregno = regno, @amount = credit, 
            @remark = remark, @trans_type = transtype 
        FROM
            #temp 
        WHERE
            ACID = (SELECT TOP 1 ACID FROM #temp)

        --begin tran
        --my function
        --commit tran

        DELETE FROM #temp 
        WHERE ACID = @sValue --remove processed record
    END
END

DROP TABLE #temp

This procedure is working as I wanted. It will be used by the members in the software.

My question is that as this code will be run by multiple users at the same time online, is this possible to create multiple temp table in the server as the same time?

I can't see why this needs to be a `CURSOR` or `WHILE` loop. You can delete multiple rows with a `DELETE` statement, and would be far faster than an RBAR solution. Maybe cause of the function? But if so, a use an inline table-turning function. — Thom A, Jul 29 '18 at 22:01
I agree with @mjwills, this is a duplicate. But, I have to say something about your approach. Although you are not **explicitly** using cursors, you are still using a loop. Cursors are not bad per-se, and are the only solution for given cases. You, eager of getting rid of cursors, 'just because', introduced a way worst approach: you coded the whole cursor scrolling logic. Sorry, but you reinvented the wheel, coded a cursor loop with some clauses with potential performance penalties. — Marcus Vinicius Pompeu, Jul 29 '18 at 22:37
thank you all for comments. I have avoided using cursor after reading this article https://searchsqlserver.techtarget.com/tip/Avoid-cursors-in-SQL-Server-with-these-methods-to-loop-over-records . But the code is running fine for me. but i want to know that if multiple users calls the same function as the same time, will the temporary table cause any problem? — Bangla Tiger, Jul 30 '18 at 11:10

Temporary table in SQL Server to avoid cursor

0 Answers0