March 12, 2010 at 9:12 pm
Hi Paul White, here are my schemas:
/*To create Question Table */
create Table Question(
QuestionID int not null
,Title varchar(max) not null
,Answer1 varchar(max) not null
,Answer2 varchar(max) not null
,Answer3 varchar(max) not null
,Answer4 varchar(max) not null
,CorrectAnswer tinyint not null
,QuestionOrder tinyint not null
,QuizID int not null
constraint PK_Question primary key(QuestionID )
contraint FK_Quiz_name foreign key(,QuizID ) references Quiz(QuizID));
/*To Populate Question Table */
INSERT INTO [dbo].[Question]
([Title]
,[Answer1]
,[Answer2]
,[Answer3]
,[Answer4]
,[CorrectAnswer]
,[QuestionOrder]
,[QuizID])
VALUES
('when was WW2 ended?'
,'1954'
,'1944'
,'1943'
,'1930'
,2
,1
,1)
GO
INSERT INTO [dbo].[Question]
([Title]
,[Answer1]
,[Answer2]
,[Answer3]
,[Answer4]
,[CorrectAnswer]
,[QuestionOrder]
,[QuizID])
VALUES
('who won the 1998 world cup?'
,'Italy'
,'Mexico'
,'Japan'
,'France'
,4
,2
,1)
GO
INSERT INTO [dbo].[Question]
([Title]
,[Answer1]
,[Answer2]
,[Answer3]
,[Answer4]
,[CorrectAnswer]
,[QuestionOrder]
,[QuizID])
VALUES
('which US president declared war on Irag?'
,'Bill Clinton'
,'Thomas Jefferson'
,'George W. Bush'
,'Roosevelt'
,3
,3
,1)
GO
INSERT INTO [dbo].[Question]
([Title]
,[Answer1]
,[Answer2]
,[Answer3]
,[Answer4]
,[CorrectAnswer]
,[QuestionOrder]
,[QuizID])
VALUES
('what is the capital of China?'
,'Limkokwing'
,'Shanghai'
,'Beijing'
,'Tokyo'
,3
,4
,1)
GO
INSERT INTO [dbo].[Question]
([Title]
,[Answer1]
,[Answer2]
,[Answer3]
,[Answer4]
,[CorrectAnswer]
,[QuestionOrder]
,[QuizID])
VALUES
('which of these animals is an endangered specie?'
,'Wilder beast'
,'Girraffe'
,'Dinosaurs'
,'Siberian Tiger'
,4
,5
,1)
GO
/*To create Quiz Table */
create Table Quiz(
QuizID in not null
,Description varchar(max)
,Category nvarchar(50), null
,topNumber int null
constraint PK_tests primary key(QuizID));
/*To Populate Quiz Table */
INSERT INTO [dbo].[Quiz]
([QuizID]
,[Title]
,[Description]
,[Category]
,[topNumber])
VALUES
(1
,'Current Affairs'
,'Test how good you are with World politics'
,'Multiple Choice'
,5)
GO
INSERT INTO [dbo].[Quiz]
([QuizID]
,[Title]
,[Description]
,[Category]
,[topNumber])
VALUES
(2
,'Networking'
,'Test you understanding of Network topologies, Client-server
architecture, Multiplexing'
,'Multiple Choice'
,5)
GO
INSERT INTO [dbo].[Quiz]
([QuizID]
,[Title]
,[Description]
,[Category]
,[topNumber])
VALUES
(3
'Database Administration'
,'Test your knowledge about database architecture, Normalization,
Database mapping Transaction control'
,'Multiple Choice'
,5)
GO
/*To create question Randomization procedure */
Create procedure [dbo].[questionSelector]
As
Declare @QuizID int
SELECT iTVF.*
FROM dbo.Quiz Z
CROSS
APPLY (
SELECT TOP (Z.topNumber)
Q.QuestionID,
Q.Title,
Q.Answer1,
Q.Answer2,
Q.Answer3,
Q.Answer4,
Q.CorrectAnswer,
Q.QuestionOrder
FROM dbo.Question Q
WHERE Q.QuizId = Z.QuizId
ORDER BY
NEWID()
) iTVF
WHERE Z.QuizID = @QuizID;
March 12, 2010 at 10:23 pm
I have corrected the missing columns, spelling errors, and ordering of your script.
I do wish you had tested it before submitting - I do not like wasting my time.
Nevertheless, once the errors in your script are corrected, my previous submission works perfectly.
I do not understand why you have had such trouble with this, to be frank about it.
USE tempdb;
/*To create Quiz Table */
create Table Quiz(
QuizID int not null
,Title nvarchar(50)
,Description varchar(max)
,Category nvarchar(50) null
,topNumber int null
constraint PK_tests primary key(QuizID));
/*To Populate Quiz Table */
INSERT INTO [dbo].[Quiz]
([QuizID]
,[Title]
,[Description]
,[Category]
,[topNumber])
VALUES
(1
,'Current Affairs'
,'Test how good you are with World politics'
,'Multiple Choice'
,5)
GO
INSERT INTO [dbo].[Quiz]
([QuizID]
,[Title]
,[Description]
,[Category]
,[topNumber])
VALUES
(2
,'Networking'
,'Test you understanding of Network topologies, Client-server
architecture, Multiplexing'
,'Multiple Choice'
,5)
GO
INSERT INTO [dbo].[Quiz]
([QuizID]
,[Title]
,[Description]
,[Category]
,[topNumber])
VALUES
(3,
'Database Administration'
,'Test your knowledge about database architecture, Normalization,
Database mapping Transaction control'
,'Multiple Choice'
,5)
GO
/*To create Question Table */
create Table Question(
QuestionID int identity not null
,Title varchar(max) not null
,Answer1 varchar(max) not null
,Answer2 varchar(max) not null
,Answer3 varchar(max) not null
,Answer4 varchar(max) not null
,CorrectAnswer tinyint not null
,QuestionOrder tinyint not null
,QuizID int not null
constraint PK_Question primary key(QuestionID )
constraint FK_Quiz_name foreign key(QuizID) references Quiz(QuizID));
/*To Populate Question Table */
INSERT INTO [dbo].[Question]
([Title]
,[Answer1]
,[Answer2]
,[Answer3]
,[Answer4]
,[CorrectAnswer]
,[QuestionOrder]
,[QuizID])
VALUES
('when was WW2 ended?'
,'1954'
,'1944'
,'1943'
,'1930'
,2
,1
,1)
GO
INSERT INTO [dbo].[Question]
([Title]
,[Answer1]
,[Answer2]
,[Answer3]
,[Answer4]
,[CorrectAnswer]
,[QuestionOrder]
,[QuizID])
VALUES
('who won the 1998 world cup?'
,'Italy'
,'Mexico'
,'Japan'
,'France'
,4
,2
,1)
GO
INSERT INTO [dbo].[Question]
([Title]
,[Answer1]
,[Answer2]
,[Answer3]
,[Answer4]
,[CorrectAnswer]
,[QuestionOrder]
,[QuizID])
VALUES
('which US president declared war on Irag?'
,'Bill Clinton'
,'Thomas Jefferson'
,'George W. Bush'
,'Roosevelt'
,3
,3
,1)
GO
INSERT INTO [dbo].[Question]
([Title]
,[Answer1]
,[Answer2]
,[Answer3]
,[Answer4]
,[CorrectAnswer]
,[QuestionOrder]
,[QuizID])
VALUES
('what is the capital of China?'
,'Limkokwing'
,'Shanghai'
,'Beijing'
,'Tokyo'
,3
,4
,1)
GO
INSERT INTO [dbo].[Question]
([Title]
,[Answer1]
,[Answer2]
,[Answer3]
,[Answer4]
,[CorrectAnswer]
,[QuestionOrder]
,[QuizID])
VALUES
('which of these animals is an endangered specie?'
,'Wilder beast'
,'Girraffe'
,'Dinosaurs'
,'Siberian Tiger'
,4
,5
,1)
GO
/*To create question Randomization procedure */
CREATE PROCEDURE dbo.QuestionSelector
@QuizID INTEGER
AS
BEGIN
SELECT iTVF.*
FROM dbo.Quiz Z
CROSS
APPLY (
SELECT TOP (Z.topNumber)
Q.QuestionID,
Q.Title,
Q.Answer1,
Q.Answer2,
Q.Answer3,
Q.Answer4,
Q.CorrectAnswer,
Q.QuestionOrder
FROM dbo.Question Q
WHERE Q.QuizId = Z.QuizId
ORDER BY
NEWID()
) iTVF
WHERE Z.QuizID = @QuizID;
END
GO
-- This works fine
EXECUTE dbo.QuestionSelector @QuizID = 1;
GO
DROP PROCEDURE dbo.QuestionSelector;
DROP TABLE dbo.Question, dbo.Quiz;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 13, 2010 at 12:04 am
Hi paul,
I totally apologize for the troubles, as it is i'm still mastering T/SQL programming. I just executed the questionSelector procedure and i got this error:
Msg 8146, Level 16, State 2, Procedure questionSelector, Line 0
Procedure questionSelector has no parameters and arguments were supplied
March 13, 2010 at 12:32 am
shawndidy (3/13/2010)
I totally apologize for the troubles, as it is i'm still mastering T/SQL programming. I just executed the questionSelector procedure and i got this error:Msg 8146, Level 16, State 2, Procedure questionSelector, Line 0
Procedure questionSelector has no parameters and arguments were supplied
Use the definition of the procedure I just posted :satisfied:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 15, 2010 at 5:07 pm
hi paul i did:
Create procedure QuestionSelector(@QuizID int)
As
Select statements.
Then i exec QuestionSelector @QuizID = 1; and its working thanks alot. I appreciate your patience and i'm really grateful
March 15, 2010 at 9:38 pm
No worries. I am glad we got there in the end. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply