I am trying to insert data into one table, select from another table based on if condition but is not working for either if condition. Parameteres are provided corectly, I've checked that. What am I doing wrong?
CREATE PROCEDURE [dbo].[AddTestDetail]
@IdUserTest AS int,
@Complexitate AS nvarchar
AS
BEGIN
IF (@Complexitate = 'GENERAL')
BEGIN
INSERT INTO tblTestDetail (IdIntrebare, IdUserTest)
SELECT ti.IdIntrebare AS IdIntrebare, @IdUserTest
FROM (SELECT TOP 10 * FROM tblIntrebare ORDER BY NEWID()) ti JOIN tblRaspuns tr ON ti.IdIntrebare = tr.IdIntrebare JOIN tblProcedura tp ON ti.IdProcedura = tp.IdProcedura
WHERE tp.Sectiune = 'GENERAL';
END
IF (@Complexitate = 'SPECIFIC')
BEGIN
INSERT INTO tblTestDetail (IdIntrebare, IdUserTest)
SELECT ti.IdIntrebare AS IdIntrebare, @IdUserTest
FROM (SELECT TOP 10 * FROM tblIntrebare ORDER BY NEWID()) ti JOIN tblRaspuns tr ON ti.IdIntrebare = tr.IdIntrebare JOIN tblProcedura tp ON ti.IdProcedura = tp.IdProcedura
WHERE tp.Sectiune = 'GENERAL';
INSERT INTO tblTestDetail (IdIntrebare, IdUserTest)
SELECT ti.IdIntrebare AS IdIntrebare, @IdUserTest
FROM (SELECT TOP 10 * FROM tblIntrebare ORDER BY NEWID()) ti JOIN tblRaspuns tr ON ti.IdIntrebare = tr.IdIntrebare JOIN tblProcedura tp ON ti.IdProcedura = tp.IdProcedura
WHERE tp.Sectiune = 'SPECIFIC';
END
END
Marius VasilePosted Sep 17, 2023, 7:15 AM
I tried with your suggestion. The problem I have now is that
is not returning 10 questions. In the table I have almost 50 questions. Why?
Anupam MaitiPosted Sep 17, 2023, 7:02 AM
Added the correct data type for the
@Complexitateparameter. Can you try with this.Marius VasilePosted Sep 17, 2023, 7:00 AM
Thank you Anupam,
For the second block I try to insert 10 from GENERAL and 10 from SPECIFIC. And it should be random questions that's why I used ORDER BY NEWID()
Using only TOP 10 gives me first 10 questions in the table that meets condition, right?
Anupam MaitiPosted Sep 17, 2023, 6:56 AM
The SELECT statements for both 'GENERAL' and 'SPECIFIC' conditions are the same in the second block. Can you try below?
If it is working, Please accept my answer.