Syntax Help Needed
-
Hello,
I am trying to set up a Stored Procedure that will evaluate the value of a Bit type field called 'Incomplete'. CASE Incomplete = 1, an Insert into a table using data from two other tables occurs. CASE Incomplete = 0, a delete occurs against the table upon which the Insert occured.
I have tested the Insert and Delete statements individually, and they work fine. My problem is getting them to both work within the following code:
************************************
SELECT Incomplete = CASE
WHEN Incomplete = 1 THEN
INSERT tblMMIncompleteRC
SELECT
SD.Permnum,
SD.FirstName,
SD.LastName,
TD.SystemUserName
FROM Student_Data_Main SD INNER JOIN Teacher_Data_Main TD
ON SD.TeacherID=TD.TeacherID
WHERE SD.Permnum NOT IN (SELECT IR.Permnum from tblMMIncompleteRC IR) AND SD.Permnum = '604'
WHEN Incomplete = 0 THEN
DELETE tblMMIncompleteRC
WHERE Permnum = '604'
END
FROM tblRCStudentGrades
***********************************
The Permnum '604' will be replaced with a parameter in the final code. I am getting syntax errors at the first 'INSERT' statement, the second 'WHEN' statement, and at END. I have tried several variations of this code without success.
Please let me know what adjustments should be made to this code so that it will work.
Thanks for your help!
CSDunn
-
The CASE statement can only return expressions, not execute statements. It is intended to be used to return a value within a SELECT list. The IF..ELSE statement is what you need, except it can't iterate through multiple rows, if that's what you're trying to do. To do that you'll need to use a cursor, testing each row with IF.
Jay Madren
Jay Madren -
You can't put the INSERTS and DELETES inside a CASE statement.
Is there a link between tblRCStudentGrades and the other tables? If so you could do it this way,
INSERT tblMMIncompleteRC
SELECT
SD.Permnum,
SD.FirstName,
SD.LastName,
TD.SystemUserName
FROM Student_Data_Main SD
INNER JOIN Teacher_Data_Main TD
ON SD.TeacherID=TD.TeacherID
INNER JOIN tblRCStudentGrades SG
ON ??? = ???
LEFT JOIN tblMMIncompleteRC IR
ON SD.Permnum = IR.Permnum
WHERE SD.Permnum = '604'
AND SG.Incomplete = 1
AND IR.Permnum IS NULL... and for the delete
DELETE tblMMIncompleteRC
WHERE Permnum = '604'
AND ??? IN (
SELECT ???
FROM tblRCStudentGrades SG
WHERE SG.Incomplete = 0
)Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Thanks for your help! CSDunn
-
The following tested solution combines both of the suggested ideas, and accomplishes my objective:
******************************************
Create Procedure MMIncompleteRC_sp
@Permnum varchar (12)
AS
IF
(SELECT RC.Incomplete
FROM
tblRCStudentGrades RC
WHERE RC.Permnum = @Permnum) = 1
BEGIN
INSERT tblMMIncompleteRC
SELECT
SD.Permnum,
SD.FirstName,
SD.LastName,
TD.SystemUserName
FROM Student_Data_Main SD
INNER JOIN Teacher_Data_Main TD ON SD.TeacherID = TD.TeacherID
INNER JOIN tblRCStudentGrades SG ON SG.Permnum = SD.Permnum
LEFT JOIN tblMMIncompleteRC IR ON SD.Permnum = IR.Permnum
WHERE SD.Permnum = @Permnum
AND SG.Incomplete = 1
AND IR.Permnum IS NULL
END
IF
(SELECT RC.Incomplete
FROM
tblRCStudentGrades RC
WHERE RC.Permnum = @Permnum) = 0
BEGIN
Delete tblMMIncompleteRC
where Permnum = @Permnum
END
****************************************
Thanks again for your help.
CSDunn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply