Help with my sql server stored procedure

I have a stored procedure to which i wish to pass an additional parameter @ROW_TO_RETURN

How do i change the sql query. ( Instead of the top 50 which i am retrieving in the query below, i wish to use the input parameter to specify the row to returns.

SELECT TOP 50
      QUESTION_ID,
      CORRECT_ANSWER_ID,
      MARK_ALLOCATED,
      NEWID()ID
FROM CERTIFYMAX_QUESTIONBANK_VIEW
WHERE SUBJECT_ID=@SUBJECT_ID AND (TOPIC_ID=@TOPIC_ID or @TOPIC_ID = -2) ORDER BY NEWID()

CREATE PROCEDURE [dbo].[CertifyMax_ExamSetup_GenerateExamQuestions]
(
@SUBJECT_ID int,
@TOPIC_ID int,
@ROW_TO_RETURN int,
@EXAM_REFERENCE_CODE nvarchar(50)
)
AS
SET NOCOUNT ON;
 
INSERT INTO CERTIFYMAX_EXAMINATION_QUESTIONS 
(
	QUESTION_ID,
	CORRECT_ANSWER_ID,
	MARK_ALLOCATED,
	EXAM_REFERENCE_CODE,
	COUNT_NO
)
SELECT 
	QUESTION_ID,
	CORRECT_ANSWER_ID,
	MARK_ALLOCATED,
	@EXAM_REFERENCE_CODE,
	ROW_NUMBER() OVER(ORDER BY ID) 
FROM
(
SELECT TOP 50
	QUESTION_ID,
	CORRECT_ANSWER_ID,
	MARK_ALLOCATED,
	NEWID()ID
FROM CERTIFYMAX_QUESTIONBANK_VIEW 
WHERE SUBJECT_ID=@SUBJECT_ID AND (TOPIC_ID=@TOPIC_ID or @TOPIC_ID = -2) ORDER BY NEWID()
) tmp

Open in new window

TECH_NETAsked:
Who is Participating?
 
momi_sabagCommented:
SELECT TOP (@ROW_TO_RETURN)
      QUESTION_ID,
      CORRECT_ANSWER_ID,
      MARK_ALLOCATED,
      NEWID()ID
FROM CERTIFYMAX_QUESTIONBANK_VIEW
WHERE SUBJECT_ID=@SUBJECT_ID AND (TOPIC_ID=@TOPIC_ID or @TOPIC_ID = -2) ORDER BY NEWID()
0
 
petr_hlucinCommented:
This one should help.
SELECT 
      QUESTION_ID,
      CORRECT_ANSWER_ID,
      MARK_ALLOCATED,
      NEWID()ID,
      ROW_NUMBER() Over (ORDER BY ID) as RowID
FROM CERTIFYMAX_QUESTIONBANK_VIEW 
WHERE SUBJECT_ID=@SUBJECT_ID AND (TOPIC_ID=@TOPIC_ID or @TOPIC_ID = -2) 
AND RowID <= 50
ORDER BY NEWID()

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Additional note to asker.

ORDER BY NEWID()

You are essentially creating new uniqueidentifiers for sake of ordering.  Since you have already created these in the select statement through:

NEWID() AS ID

You can order by the column number, in your case 4.

ORDER BY 4

Just an FYI.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.