|
Subject:
|
Please check my SP
|
|
Posted By:
|
mat41
|
Post Date:
|
4/19/2006 2:42:26 AM
|
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
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
4/19/2006 5:36:08 AM
|
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
|
|
