in Technology by
What is a Recursive Stored Procedure?

1 Answer

0 votes
by

A stored procedure which calls itself until a boundary condition is reached, is called a recursive stored procedure. This recursive function helps the programmers to deploy the same set of code several times as and when required. Some SQL programming languages limit the recursion depth to prevent an infinite loop of procedure calls from causing a stack overflow, which slows down the system and may lead to system crashes.

DELIMITER $$ 			 /* Set a new delimiter => $$ */
CREATE PROCEDURE calctotal(	
    IN number INT,			
    OUT total INT
) BEGIN
DECLARE score INT DEFAULT NULL; 	 
SELECT awards FROM achievements 	 
WHERE id = number INTO score;
IF score IS NULL THEN SET total = 0; 	 
ELSE
CALL calctotal(number+1); 	 
SET total = total + score; 	 
END IF;
END $$ 			 
DELIMITER ; 			 

    Related questions

    0 votes
    0 votes
        What are the advantages of stored procedure?...
    asked Dec 17, 2020 in Technology by JackTerrance
    0 votes
        What is a Stored Procedure?...
    asked Dec 11, 2020 in Technology by JackTerrance
    0 votes
        What is the command used to Recompile the stored procedure at run time?...
    asked Nov 9, 2020 in Technology by Editorial Staff
    0 votes
        Which SQL server table is used to hold the stored procedure scripts?...
    asked Nov 9, 2020 in Technology by Editorial Staff
    0 votes
        What are the differences between Stored Procedure and the dynamic SQL?...
    asked Nov 9, 2020 in Technology by Editorial Staff
    0 votes
        I'm attempting to call my sql stored procedure which takes RaceDate as an input and returns Location as an ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
    asked May 7, 2022 in Education by JackTerrance
    0 votes
        I'm attempting to call my sql stored procedure which takes RaceDate as an input and returns Location as an ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
    asked May 4, 2022 in Education by JackTerrance
    0 votes
        I have a stored procedure in SQL 2005. The Stored Procedure is actually creating temporary tables in the ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
    asked Mar 19, 2022 in Education by JackTerrance
    0 votes
        I have a stored procedure in SQL 2005. The Stored Procedure is actually creating temporary tables in the ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
    asked Mar 18, 2022 in Education by JackTerrance
    0 votes
        I have a stored procedure in SQL 2005. The Stored Procedure is actually creating temporary tables in the ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
    asked Mar 18, 2022 in Education by JackTerrance
    0 votes
        Which of the following is used to call stored procedure? (a) Statement (b) PreparedStatement (c) CallableStatment (d ... & JDBC of Java Select the correct answer from above options...
    asked Feb 22, 2022 in Education by JackTerrance
    0 votes
        Simple select stored procedure but getting only one column as the result others were getting as null. Can ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
    asked Feb 7, 2022 in Education by JackTerrance
    0 votes
        Simple select stored procedure but getting only one column as the result others were getting as null. Can ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
    asked Feb 5, 2022 in Education by JackTerrance
    0 votes
        How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [ ... not supply any columns. Select the correct answer from above options...
    asked Jan 28, 2022 in Education by JackTerrance
    ...