Solved

Help with SQL Query

Posted on 2009-05-12
5
184 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 142

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS how to COMPARE a data column from different servers? 6 98
SQL Agent Timeout 5 59
Query to return total 6 19
Email Notifications for SQL 2005 9 28
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

809 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