Solved

Help with my sql server stored procedure

Posted on 2008-10-03
3
207 Views
Last Modified: 2010-03-19
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

0
Comment
Question by:TECH_NET
3 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22639832
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
 
LVL 4

Expert Comment

by:petr_hlucin
ID: 22639833
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22639926
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL 2012 AOG and SQL2014 AOG 76 61
MS SQL Sever Import/export problem 7 48
Need help with a query 3 39
Need SSIS project 2 28
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question