0

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 : enter image description here

Improve this question
7
  • 1
    You don't DECLARE an OUTPUT parameter like that, the parameter is included as part of the parameter list. Also, the error you get has nothing to do with OUTPUT parameters... That error is about your subquery and it is literally telling you the problem: "Subquery returned more than 1 value." You are trying to assign a scalar variable multiple values; that isn't allowed as it's a scalar variable. What are you actually trying to achieve here? Commented Feb 8, 2022 at 12:51
  • The TicketHistory table inserts a row on any such event on the ticket. For e.g, if the user put the ticket status to 'Work In Progress' a new row is inserted into the table along with the current_timestamp. So, basically the ticket might go through 'Resolved' status multiple times but I want to capture only the recent timestamp which has 'Resolved' status and which is 5 days old, that's why I have used the MAX(TICKETTIME) Commented Feb 8, 2022 at 13:00
  • 2
    Also why the loop? As written it is an endless loop. But truly you don't need a loop for inserts like this. This whole process seems to be fatally flawed with the row by agonizing row approach. Commented Feb 8, 2022 at 13:48
  • Hmmm....your comment says you want to do an update but your code is doing an insert. Seems to me this entire looping construct could be changed to just be an update. Commented Feb 8, 2022 at 13:53
  • 1
    Still don't get why this needs a loop. You can do an UPDATE or INSERT with a join Commented Feb 8, 2022 at 14:41

2 Answers 2

Reset to default
2

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.

--EDIT--

With the clarification from the OP's comment all that need to happen is add the insert above this select

insert into TicketHistory (CURRENTSTATUS_ANALYST,TICKETNUMBER,PREVIOUSSTATUS_ANALYST,TICKETTIME,FIELD,CREATEDBY)
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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

The query I have posted gives me the desired results, but it's just that I need to return the list of ticket numbers in a variable and eventually use those results of the variable and make another insert query against the same ticket number returned by the previous select query.
Your query does the same what I had posted but the challenge is in how to store list of elements in the stored procedure variable and pass it on to the next variable to insert the data ?
No idea what you mean by that. What elements are you trying to store? If you can share the whole problem I would be happy to help you fix it. But only having part of the problem makes it difficult to find the best solution.
Alright. Here is the problem statement. I need to get a list of all tickets which are currently in 'Resolved' state and been older than 5 days. Once I get the list, all I want is to close all of them. And this operation would basically be a 'INSERT' statement marking ÇURRENTSTATUS_ANALYST column as 'CLOSED' and PREVIOUSSTATUS_ANALYST column as 'RESOLVED'... Hope this clarifies !!
OK. All you need to do then is to add a single live above the query I posted to make it an insert.
0

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
Improve this answer

5 Comments

Glad you solved this but you really need to stop using cursors for inserts like this. It is horribly inefficient.
so what should be the efficient solution ? If you could guide, it would be great.
Look at the solution I posted. It is a single insert instead of looping.
But as per the problem statement, if the SELECT query returns multiple rows, then a single INSERT wouldn't be the right solution, isn't it ? I hope you would agree on this. Because what I intend to achieve is close all the tickets at one go, per the match returned by the above SELECT query.
Yes I get that. A single select statement can return multiple rows, like the example I posted. It would insert all the rows just like your loop but in a single statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.