Use an output parameter in a TSQL procedure, then call that procedure within a cursor to update a table?

Question

I have tried altering the table within the procedure; however, given the table is created or dropped afterwards, this doesn't work, I guess?

I attempted to create the table before this, then inserting the values from the procedure into the table. Failed. Doesn't even run.

No matter what I do, (table value) tvWin is always null...

The goal is to create a rock paper scissors game and using a Cursor to iterate through a table of values "Rock/paper/scissors". That part is easy enough, I think.

I'm only getting null values for tvWin. Which I guess means I'm having an issue at the point of transferring the procedure to update the table values.

It seems extremely convoluted, but that's how I'm supposed to do it. Create variables within a procedure, create the code to run; create empty variables outside the procedure to store iterated values from the Cursor; then, call the values from the procedure within the Cursor. I mean, this is starting to feel like Inception.

Working Cursor code:

USE scratch_DB;

DROP TABLE IF EXISTS tblRPS_data;

GO
-----

CREATE TABLE tblRPS_data
(
 tvSeq INT IDENTITY PRIMARY KEY,
 tvP1 CHAR(1),
 tvP2 CHAR(1),
 tvWin CHAR(1)
);

GO
-----

INSERT INTO tblRPS_data(tvP1, tvP2)
VALUES ( 'R', 'R' ),
  ( 'R', 'P' ),
  ( 'R', 'S' ),
  ( 'P', 'R' ),
  ( 'P', 'P' ),
  ( 'P', 'S' ),
  ( 'S', 'R' ),
  ( 'S', 'P' ),
  ( 'S', 'S' );

DECLARE @lvSeq CHAR(1), @lvP1 CHAR(1), @lvP2 CHAR(1), @lvWin CHAR(1);

IF CURSOR_STATUS('global','csrMyCursor')>=-1
BEGIN
 DEALLOCATE csrMyCursor;
END

 DECLARE csrMyCursor CURSOR
    FOR
    SELECT tvSeq, tvP1, tvP2
    FROM tblRPS_data;

OPEN csrMyCursor;

FETCH NEXT FROM csrMyCursor
INTO @lvSeq, @lvP1, @lvP2;

WHILE (@@FETCH_STATUS = 0)

BEGIN

IF @lvP1 NOT IN ('R','P','S') OR @lvP1 NOT IN ('R','P','S')
    SET @lvWin = 0;

    ELSE IF @lvP1 + @lvP2 IN ('RS', 'PR', 'SP')
        SET @lvWin = 1;

        ELSE IF @lvP1 = @lvP2 
        SET @lvWin=3;

        ELSE 
        SET @lvWin = 2;

        IF @lvWin = 3
        PRINT 'It is a tie'
        
        ELSE 
        PRINT 'The Winner is Player ' + @lvWin;

UPDATE tblRPS_data
SET tvWin = @lvWin
WHERE tvSeq=@lvSeq;

FETCH NEXT FROM csrMyCursor
INTO @lvSeq, @lvP1, @lvP2;

END

CLOSE csrMyCursor;
DEALLOCATE csrMyCursor;

SELECT *
FROM tblRPS_data;

Output is good.

Now, how do I put this procedure in the cursor instead of the direct code?

USE scratch_DB;

GO

CREATE OR ALTER PROCEDURE udpCalcWinner( @parmP1 AS CHAR(1), @parmP2 AS CHAR(1) ) --creates procedure (program) with 2 inputs

AS 

BEGIN

DECLARE @lvP1 AS CHAR(1), @lvP2 AS CHAR(1), @lvWin AS TINYINT;

IF @lvP1 NOT IN ('R','P','S') OR @lvP1 NOT IN ('R','P','S')
    SET @lvWin = 0;

    ELSE IF @lvP1 + @lvP2 IN ('RS', 'PR', 'SP')
        SET @lvWin = 1;

        ELSE IF @lvP1 = @lvP2 
        SET @lvWin=3;

        ELSE 
        SET @lvWin = 2;

    RETURN 0;

END;

DROP TABLE IF EXISTS tblRPS_data;

CREATE TABLE tblRPS_data
(
 tvSeq INT IDENTITY PRIMARY KEY,
 tvP1 CHAR(1),
 tvP2 CHAR(1),
 tvWin CHAR(1)
);

GO
-----

INSERT INTO tblRPS_data(tvP1, tvP2)
VALUES  ( 'R', 'R' ),
        ( 'R', 'P' ),
        ( 'R', 'S' ),
        ( 'P', 'R' ),
        ( 'P', 'P' ),
        ( 'P', 'S' ),
        ( 'S', 'R' ),
        ( 'S', 'P' ),
        ( 'S', 'S' );

DECLARE @lvSeq AS INT, @lvP1 AS CHAR(1), @lvP2 AS CHAR(1), @lvWin AS CHAR(1);

IF CURSOR_STATUS('global','csrMyCursor')>=-1
BEGIN
 DEALLOCATE csrMyCursor;
END

 DECLARE csrMyCursor CURSOR
    FOR
    SELECT tvSeq, tvP1, tvP2
    FROM tblRPS_data;

OPEN csrMyCursor;

FETCH NEXT FROM csrMyCursor
INTO @lvSeq, @lvP1, @lvP2;

WHILE (@@FETCH_STATUS = 0)
-------------------------------------------
BEGIN

DECLARE @lvI AS INT;
    
EXECUTE @lvI = udpCalcWinner '@lvP1', '@lvP2';

    UPDATE tblRPS_data
    SET tvWin = @lvWin
    WHERE tvSeq = @lvSeq;

    FETCH NEXT FROM csrMyCursor
    INTO @lvSeq, @lvP1, @lvP2;
END;

CLOSE csrMyCursor;
DEALLOCATE csrMyCursor;

SELECT * 
FROM tblRPS_data;

Result is 9 rows, as it should; and, all Null values in the tvWin column, unlike in just the cursor code.

Answer 1

create procedure udpCalcWinner @lvP1 char(1), @lvP2 char(1), @lvWin char(1) output
.......
select @lvP1 = isnull(@lvP1, '-'), @lvP2 = isnull(@lvP2, '-') --handle null input

IF @lvP1 NOT IN ('R','P','S') OR @lvP2 NOT IN ('R','P','S')
    SET @lvWin = '0'; --@lvWin is char...quotes ''
..............................................................
        else if @lvWin = '0'
        print 'invalid input'

        ELSE 
        PRINT 'The Winner is Player ' + @lvWin;
GO

 .........cursor loop .........
 EXECUTE udpCalcWinner @lvP1 = @lvP1, @lvP2=@lvP2, @lvWin = @lvWin output;