The following SP is not executing properly (doesnt do the inserts however does the updates) FYI we are connecting during testing usignt he SA account to eliminate permission problems:

CREATE PROCEDURE sp_UpdateCourse
(
    @Code int,
    @Description varchar(72),
    @School varchar(64),
    @MaxStudents int,
    @CourseDuration real,
    @MinRank varchar(3),
    @maxRank varchar(3),
    @Inactive bit
)
AS
    DECLARE @MinSeq int
    DECLARE @MaxSeq int
    DECLARE @code2 int
    DECLARE @Seq int
    DECLARE @Rank varchar(3)

    IF @MinRank IS NULL
        SET @MinSeq = 100
    ELSE
        SET @MinSeq = (SELECT SeqOrder FROM Ranks WHERE Code = @MinRank)
    IF @MaxRank IS NULL
        SET @MaxSeq = 0
    ELSE
        SET @MaxSeq = (SELECT SeqOrder FROM Ranks WHERE Code = @MaxRank)
    IF @MinSeq < @MaxSeq
    BEGIN
        SET @Seq = @MinSeq
        SET @MinSeq = @MaxSeq
        SET @MaxSeq = @Seq
        SET @Rank = @MinRank
        SET @MinRank = @MaxRank
        SET @MaxRank = @Rank
    END

    SET @Code2 = (SELECT Code FROM Courses WHERE Code = @Code)
    IF ((@Code2 = null) OR ( @Code2 <> @Code))
        INSERT INTO Courses (Code, Description, School,MaxStudents,
            CourseDuration, MinRank, MaxRank,MinSeq,MaxSeq, Inactive,Updated)
        VALUES (@Code,@Description,@school, @MaxStudents,
            @CourseDuration,@minRank,@maxRank,@MinSeq,@MaxSeq, @Inactive,1)
    ELSE
        UPDATE Courses SET
            Description = @Description,
            School = @school,
            MaxStudents = @MaxStudents,
            CourseDuration = @CourseDuration,
            MinRank = @MinRank,
            MaxRank = @MaxRank,
            MinSeq = @MinSeq,
            MaxSeq = @MaxSeq,
            Inactive = @Inactive,
            Updated = 1
            WHERE Code = @Code
GO


BTW: I dont write to many SP's. I have a couple of questions:

1..When I click the 'Check Syntax' button it is problem free. Why If I change a table name to be incorrect (table does not exist) does it still say problem free?

2..If I change a variable name or field name it says there is a problem

This SP gets executed in line in an ASP page. I hope I have given all required information.

TYIA


Wind is your friend
Matt

__________________
Wind is your friend
Matt

First off, you cannot test whether something is equal to NULL. Nothing is ever equal to NULL, or not equal to NULL, for that matter. Any comparison whatsoever to NULL will always fail, i.e. be interpreted as false.

You must test to see if something is NULL. An annoying distinction, perhaps, but there it is. Thus, your line in the IF which controls execution of the INSERT statement should read:

IF ((@Code2 IS NULL) OR ( @Code2 <> @Code))

(It's curious that you constructed the other prior tests for NULL correctly.)

As to your other questions:

The 'Check Syntax" button is unfortunately just that, a syntax check. It will find syntax errors in your SP, such as missing commas, parentheses, undeclared variables, etc. It will not find semantic errors, though, such as whether a database object actually exists. Those won't be detected until the SP is compiled, right before it is run.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com