August 7, 2011 at 3:17 pm
I see we have an existing stored procedure that does something like this:
CREATE PROCEDURE [dbo].[StudentSnapshot_Insert]
@SchoolYear INT,
@IncludeWithdrawnStudents BIT,
@createdby VARCHAR(128),
@Rowcount INT OUTPUT
AS
INSERT INTO [dbo].[StudentSnapshot]
,[StudentID]
,[HasUpdated]
,[StudentStatus]
,[WithdrawCode]
.......
from table b
WHERE
(b.WITHDRAW_CODE IS NULL OR @IncludeWithdrawnStudents = 1)
My question is the clause in the where statement, I understand the issue is at first we only want to include non-withdrawn students, but later we added @IncludeWithdrawnStudents flag to allow the inclusion of withdrawn students.
But I just don't understand how this works?
esp. or statement in where clause,
can anyone help to explain this?
Thanks
August 7, 2011 at 4:09 pm
The where clause is very straightforward.
WHERE (b.WITHDRAW_CODE IS NULL OR @IncludeWithdrawnStudents = 1)
means that the action will not be done unless either the student is not withdrawn (b.withdraw_code is null) or you are including withdrawn students (@IncludeWithdrawnStudents = 1) or (of course ) both.
Or you can think of it like this:
If @IncludeWithdrawnStudents is 1, the where clause is true whether the student is withdrawn or not, so you will copy all students from table b into StudentSnapshot
If @IncludeWithdrawnStudents is 0, the where clause is only true if teh student is not withdrawn, so you will copy only unwithdrawn students from table b into StudentSnapshot.
Tom
August 7, 2011 at 5:34 pm
Thank you.
I am still a little bit confusing.I'm new to programming. It seems I have never seen before a parameter is in where clause but it is not the field of the table.
I saw before a paramter is assigned to a field of a table in the where clause.
like where processID=@processID
I mean the @IncludeWithdrawnStudents is not a field of the table.
And also what does it mean when you say' where clause is true'
Any more examples about this on internet or sql books online?
Thanks
August 7, 2011 at 6:30 pm
WHERE clauses evaluate expressions which return true or false values. Two simple examples:
WHERE 1 = 1 -- will return ALL rows
WHERE 1 = 0 -- will return NO rows
Expressions can be made up of constant values (as shown above), values from columns, and/or values of parameters. In your case the WHERE clause is testing the value of a column (not field) in a table, but is also testing the value of a parameter.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 7, 2011 at 6:52 pm
I got it, thanks.
If i can see more examples online somewhere, that would be helpful
August 7, 2011 at 7:00 pm
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply