CODEWITHSUNDEEP
sql-serverloopsstored-procedurescursoroutput-parameter
Data Engineer
Data Engineer

Reputation: 827

CURSOR with a stored procedure with OUTPUT parameters

I have to insert one record at a time into a table and then execute a stored procedure by passing output variables.

The insert part actually does do the this and I see two different records into the destination table. But the stored procedure seems to use the same out parameters that were passed for the very first record inserted into destination table. So basically when a stored procedures is being called in loops over the same output parameters over and over again for each distinct ID that is being inserted into the destination table.

In my pseudocode below it prints 3 times the following 5, 10, 15 . Which is correct since it takes each new ID in the dbo.Table_Test. But in my actual code actually it does take only only very first ID that repeats looping over the same ID three times.

-------- CREATING STORED PROCEDURE --------
USE MyDB;
GO

DROP PROCEDURE IF EXISTS dbo.sp_Testing

USE MyDB;
GO

CREATE PROCEDURE dbo.sp_Testing
    @QueueId INT,
    -- response
    @MainId INT OUT,  -- this allows null
    @MessageTx VARCHAR(500) OUT,
    @SuccessIn BIT OUT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT * FROM MyDB.sys.tables WHERE name = 'Table_Test') --print 1

    CREATE TABLE dbo.Table_Test 
    (
        ID INT NOT NULL PRIMARY KEY IDENTITY(5,5),
        name VARCHAR(10) NULL,
        Phone INT NULL,
        category VARCHAR(10) NULL
    )

    INSERT INTO dbo.Table_Test (name)
    VALUES ('Andrew')

    SET @MainId = SCOPE_IDENTITY()
    PRINT @MainId
END

-------- END OR STORED PROCEDURE --------
GO

-------- INSERT STATEMENTS ---------
USE MyDB;

IF OBJECT_ID('tempdb..#MainTable') IS NOT NULL
    DROP TABLE #MainTable

IF OBJECT_ID('tempdb..#Queue') IS NOT NULL
    DROP TABLE #Queue

DECLARE @MessageTx VARCHAR(30)
DECLARE @SuccessIn BIT
DECLARE @QueueId INT
DECLARE @MainId INT
DECLARE @ParentId INT

SET @MainId = NULL
SET @SuccessIn = 1

CREATE TABLE #MainTable 
(
        ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
        name VARCHAR(10) NULL,
        Phone INT NOT NULL,
        category VARCHAR(10) NULL
)

INSERT INTO #MainTable (name, Phone, category)
VALUES ('Adam', 123433, 'new'),
       ('John', 222222, 'new'),
       ('Samuel', 123123313, 'new')

-- SELECT * FROM #MainTable 
-- SELECT * FROM  #Queue
-- SELECT * FROM  #Test

DECLARE Cursor_test CURSOR LOCAL FOR  
    SELECT id
    FROM #MainTable         

-- get relationships for next level 
OPEN Cursor_test  

FETCH NEXT FROM Cursor_test INTO @ParentId   

WHILE @@FETCH_STATUS = 0   
BEGIN
    IF OBJECT_ID('tempdb..#Queue') IS NOT NULL
        DROP TABLE #Queue

    CREATE TABLE #Queue
    (
        PK INT NOT NULL PRIMARY KEY identity (3,2), 
        ID INT NOT NULL
    )

    INSERT INTO #Queue (id)
        SELECT id
        FROM #MainTable

    SET @QueueId = SCOPE_IDENTITY()

    -- real-time creation
    EXEC dbo.sp_Testing @QueueId, @MainId, @MessageTx OUT, @SuccessIn OUT

    FETCH NEXT FROM Cursor_test INTO @ParentId
END

CLOSE Cursor_test   
DEALLOCATE Cursor_test

Upvotes: 0

Views: 2413

Answers (1)

Squirrel
Squirrel

Reputation: 24763

This is a bit too long to be in the comment.

Firstly you must understand that the temp table #test only exists within the stored procedure. It is created in your stored procedure and dropped once the stored procedure exits.

So every time you execute the stored procedure, it creates the temp table, when you insert the row, it return the same identity seed value which is 5.

Upvotes: 1

Related Questions