Can any one explain this Stored Procedure I am not understanding [Resolved]

Posted by Iluvdotnetfunda under Sql Server on 4/3/2012 | Points: 10 | Views : 1228 | Status : [Member] | Replies : 5
Hi Developers,

For selecting random records i found this stored Procedure i am not able to understand it. Please help me........

I am using Sql Server Database ...

DELIMITER $$

USE `vtest`$$

DROP PROCEDURE IF EXISTS `sp_random`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_random`(OUT result VARCHAR(50))
BEGIN
DECLARE rques VARCHAR(50);
DECLARE resultnotfound VARCHAR(50);
DECLARE crsrresult CURSOR FOR SELECT question FROM test WHERE done=0 ORDER BY RAND();

DECLARE CONTINUE HANDLER FOR NOT FOUND SET resultnotfound ='NO more quesstions';
OPEN crsrresult;
FETCH crsrresult INTO rques;
IF rques='' THEN
SET result=resultnotfound;
ELSE
UPDATE test SET done=1 WHERE question=rques;
SET result=rques;
END IF;
CLOSE crsrresult;
END$$

DELIMITER ;


Thanks & Regards
iluvdotnetfunda




Responses

Posted by: Rajni.Shekhar on: 4/3/2012 [Member] Bronze | Points: 50

Up
0
Down

Resolved
this sp is not displaying any record, it is just updating the records.

Thanks,
Rajni Shekhar

Iluvdotnetfunda, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 4/3/2012 [Member] Bronze | Points: 25

Up
0
Down
There is a test table having questions.
Open a cursor to read all questions one by one.
get questions into rques variable one by one using cursor loop
in loop, check if this variable is empty, throw an error of no result found using HANDLER FOR NOT FOUND
and else set done=1 in test table againt that perticular questions.

Thanks,
Rajni Shekhar

Iluvdotnetfunda, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Iluvdotnetfunda on: 4/3/2012 [Member] Starter | Points: 25

Up
0
Down
Hello Rajni,

Thanks for your Explanation.
Is this SP will display records. or it will display i sequential manner.


Thanks
iluvdotnetfunda

Iluvdotnetfunda, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Iluvdotnetfunda on: 4/3/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Rajni,

That's fine, Thanks for your contribution for my growth.

Thank you so much.

iluvdotnetfunda

Iluvdotnetfunda, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 4/3/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi,
please mark as answer if your problem is resolved to close this thread.

Thanks,
Rajni Shekhar

Iluvdotnetfunda, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response