Solved

Help with SQL Query

Posted on 2009-05-12
5
192 Views
Last Modified: 2012-05-06
I have a table EXAMINATION_QUESTIONS and a table EXAMINATION_SCHEDULER.

I wish to populate table SCHEDULER with records from a table EXAMINATION_QUESTIONS Using a conditional query.

Table EXAMINATION_QUESTIONS  needs to be populated with 2 extra column which are not present in table which is giving me the problem.

I have enclosed a query which i build and i am sure that there is some syntax in the query i am using.

ID,

CREATE PROCEDURE [dbo].[ExamMode_SetupExamQuestionforTestTaker]
(
	@REF_CODE nvarchar(50),
	@PROCESS_FLAG_ID int,
	@REQUESTOR_ID int,
	@TEST_TAKER_ID int
)
AS
SET NOCOUNT ON;
 
 
INSERT INTO EXAMINATION_SCHEDULER
(
	REQUESTOR_ID,
	EXAM_REFERENCE_CODE,
	TEST_TAKER_ID,
	QUESTION_ID,
	CORRECT_ANSWER_ID,
	MARK_ALLOCATED,
	CREATE_DATE
)
 
VALUES
(
	@REQUESTOR_ID,
	@TEST_TAKER_ID,
	SELECT
		EXAM_REFERENCE_CODE,
		QUESTION_ID,
		CORRECT_ANSWER_ID,
		MARK_ALLOCATED,
		CREATE_DATE
	FROM
		EXAMINATION_QUESTIONS
	WHERE 
		EXAM_REFERENCE_CODE=@REF_CODE  
)

Open in new window

0
Comment
Question by:TECH_NET
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24369066
Hello TECH_NET,

CREATE PROCEDURE [dbo].[ExamMode_SetupExamQuestionforTestTaker]
(
      @REF_CODE nvarchar(50),
      @PROCESS_FLAG_ID int,
      @REQUESTOR_ID int,
      @TEST_TAKER_ID int
)
AS
SET NOCOUNT ON;
 
 
INSERT INTO EXAMINATION_SCHEDULER
(
      REQUESTOR_ID,
      EXAM_REFERENCE_CODE,
      TEST_TAKER_ID,
      QUESTION_ID,
      CORRECT_ANSWER_ID,
      MARK_ALLOCATED,
      CREATE_DATE
)
 
      SELECT    @REQUESTOR_ID,
                  @TEST_TAKER_ID,
                  EXAM_REFERENCE_CODE,
                  QUESTION_ID,
                  CORRECT_ANSWER_ID,
                  MARK_ALLOCATED,
                  CREATE_DATE
      FROM
            EXAMINATION_QUESTIONS
      WHERE
            EXAM_REFERENCE_CODE=@REF_CODE  

GO


Regards,

Aneesh
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24369072
Here you go.
CREATE PROCEDURE [dbo].[ExamMode_SetupExamQuestionforTestTaker]
(
        @REF_CODE nvarchar(50),
        @PROCESS_FLAG_ID int,
        @REQUESTOR_ID int,
        @TEST_TAKER_ID int
)
AS
SET NOCOUNT ON;
 
 
INSERT INTO EXAMINATION_SCHEDULER
(
        REQUESTOR_ID,
        EXAM_REFERENCE_CODE,
        TEST_TAKER_ID,
        QUESTION_ID,
        CORRECT_ANSWER_ID,
        MARK_ALLOCATED,
        CREATE_DATE
)
 
        SELECT
        @REQUESTOR_ID,
        @TEST_TAKER_ID,
                EXAM_REFERENCE_CODE,
                QUESTION_ID,
                CORRECT_ANSWER_ID,
                MARK_ALLOCATED,
                CREATE_DATE
        FROM
                EXAMINATION_QUESTIONS
        WHERE 
                EXAM_REFERENCE_CODE=@REF_CODE  

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24369075
close:
CREATE PROCEDURE [dbo].[ExamMode_SetupExamQuestionforTestTaker]
(
      @REF_CODE nvarchar(50),
      @PROCESS_FLAG_ID int,
      @REQUESTOR_ID int,
      @TEST_TAKER_ID int
)
AS
SET NOCOUNT ON;
 
 
INSERT INTO EXAMINATION_SCHEDULER
(
      REQUESTOR_ID,
      EXAM_REFERENCE_CODE,
      TEST_TAKER_ID,
      QUESTION_ID,
      CORRECT_ANSWER_ID,
      MARK_ALLOCATED,
      CREATE_DATE
)
 
SELECT
      @REQUESTOR_ID,
      @TEST_TAKER_ID,
            EXAM_REFERENCE_CODE,
            QUESTION_ID,
            CORRECT_ANSWER_ID,
            MARK_ALLOCATED,
            CREATE_DATE
      FROM
            EXAMINATION_QUESTIONS
      WHERE 
            EXAM_REFERENCE_CODE=@REF_CODE  
)

Open in new window

0
 

Author Comment

by:TECH_NET
ID: 24370311
I have a follow up question.

I get the following error when i execute the stored procedure from my .net program.

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

I had to insert a COMMIT statement at the end of the sql query in the stored procedure to ensure the records are commited.

It is strange that the without the commit statement i do not see the inserted records although the identity column in being incremented.

Any thoughts why the above error is occuring.
0
 

Author Comment

by:TECH_NET
ID: 24370345
I had it fixed. I forgot a Begin transaction statement
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 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