How to set multiple output parameters in stored procedures-CodePudding

Is there a way to set multiple output parameters?

For example, in the stored procedure shown here, I wish to get the NUM_OF_ROWS and the TicketNumberz from the same stored procedure.

Since, I am new I don't know how to go about it...

DECLARE @TicketNumberz VARCHAR(15) OUT
DECLARE @NUM_OF_ROWS INT OUT
DECLARE @INIT INT=1 OUT

SET @TicketNumberz = (SELECT TICKETNUMBER
                      FROM TicketHistory s1 
                      WHERE TICKETTIME IN (SELECT MAX(S2.TICKETTIME) 
                                           FROM TicketHistory] S2 
                                           WHERE s1.TICKETNUMBER = S2.TICKETNUMBER)  
                        AND CURRENTSTATUS_ANALYST != 'Closed' 
                        AND CURRENTSTATUS_ANALYST = 'Resolved'
                        AND TICKETTIME < GETDATE() - 5)

-- after getting all the list of ticket numbers, update query follows to update the ticket status to 'Closed'   

WHILE (@INIT <= @NUM_OF_ROWS)
BEGIN
    INSERT INTO TicketHistory (CURRENTSTATUS_ANALYST, TICKETNUMBER, 
                               PREVIOUSSTATUS_ANALYST, TICKETTIME, FIELD, CREATEDBY)
    VALUES ('Closed', @TicketNumberz, 
            'Resolved', CURRENT_TIMESTAMP, 'Status', 'Auto.User')   
END

What this query basically does it, it would fetch all the ''Resolved' tickets which are older than 5 days and had not been 'Closed' automatically. So doing it manually through this stored procedure.

But, I am stuck because of the following error :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This is how the db looks like :

CodePudding user response：

I would probably rework the subquery in this but not crucial. It seems that perhaps that table structure is a bit of a challenge here but I think you are looking for something along these lines. This would replace all of the code you posted. There is no need for loops or variables of any kind based on what you posted.

SELECT 'Closed'
    , th.TICKETNUMBER
    , 'Resolved'
    , CURRENT_TIMESTAMP
    , 'Status'
    , 'Auto.User'
FROM TicketHistory th 
WHERE TICKETTIME IN 
(
    SELECT MAX(S2.TICKETTIME) 
    FROM TicketHistory S2 
    WHERE s1.TICKETNUMBER = S2.TICKETNUMBER
)  
    --AND CURRENTSTATUS_ANALYST != 'Closed' --there is no value that equals 'Resolved' where it could also equal 'Closed'
    AND th.CURRENTSTATUS_ANALYST = 'Resolved'
    AND th.TICKETTIME < dateadd(day, -5, GETDATE()) --use dateadd instead of shorthand

CodePudding user response：

Finally, I was able to achieve the desired result with the below code :

    --Creating a table variable
    declare @TempTable table(ticketnum varchar(50))

    --Inserting values in table variable using procedure
    insert @TempTable
    select TICKETNUMBER 
        FROM TicketHistory th 
    WHERE TICKETTIME IN 
    (
        SELECT MAX(S2.TICKETTIME) 
        FROM TicketHistory S2 
        WHERE th.TICKETNUMBER = S2.TICKETNUMBER
    )  
        AND th.CURRENTSTATUS_ANALYST = 'Resolved'
        AND th.TICKETTIME < dateadd(day, -5, GETDATE())

    --Selecting values from table variable
    SELECT * from @TempTable
            
    DECLARE @ticket_number varchar(100)
    DECLARE cur CURSOR FOR SELECT ticketnum FROM @TempTable
    OPEN cur

    FETCH NEXT FROM cur INTO @ticket_number

    WHILE @@FETCH_STATUS = 0 
        BEGIN
            insert into TicketHistory (CURRENTSTATUS_ANALYST,TICKETNUMBER,PREVIOUSSTATUS_ANALYST,TICKETTIME,FIELD,CREATEDBY)
              values('Closed', @ticket_number, 'Resolved', CURRENT_TIMESTAMP, 'Status', 'User.Auto')
            FETCH NEXT FROM cur INTO @ticket_number
        END

    CLOSE cur    
    DEALLOCATE cur
        
    END