Using COALESCE() Function
-
Hi all, I am trying to write a procedure which takes 1 parameter as @StudentID and returns Student Information for the given StudentId Parameter form Student and StudentDetail table. Now i want that if parameter is not passed(i.e NULL) then procedure should return all students information,but if StudentId passed then for that student only... I am trying this by using COALESCE function in sql server 2005. Following is DDL for my procedure.
CREATE PROCEDURE [dbo].[GetStudentById]
@StudentId AS NUMERIC
AS
BEGIN
SELECT
S.StudentId,
S.StudentFName,
S.StudentMName,
S.StudentLName,
S.Address,
SD.StudentClassId
FROM
[dbo].Student S
JOIN StudentDetail SD ON S.StudentId = SD.StudentId
WHERE
S.StudentId = COALESCE(@StudentId,NULL)
ENDBut not getting desired result ..When i am passing NULL 0 rows are returned. please help me out... Thank & Regards Dipak Thesiya
dipak
-
Hi all, I am trying to write a procedure which takes 1 parameter as @StudentID and returns Student Information for the given StudentId Parameter form Student and StudentDetail table. Now i want that if parameter is not passed(i.e NULL) then procedure should return all students information,but if StudentId passed then for that student only... I am trying this by using COALESCE function in sql server 2005. Following is DDL for my procedure.
CREATE PROCEDURE [dbo].[GetStudentById]
@StudentId AS NUMERIC
AS
BEGIN
SELECT
S.StudentId,
S.StudentFName,
S.StudentMName,
S.StudentLName,
S.Address,
SD.StudentClassId
FROM
[dbo].Student S
JOIN StudentDetail SD ON S.StudentId = SD.StudentId
WHERE
S.StudentId = COALESCE(@StudentId,NULL)
ENDBut not getting desired result ..When i am passing NULL 0 rows are returned. please help me out... Thank & Regards Dipak Thesiya
dipak
Your coalesce is wrong. Try this
WHERE S.StudentId = COALESCE(@StudentId,S.StudentId)
what you were doing is saying when (@StudentId use null, what you really wanted to say was when (@StudentId is null don't compare - which effectively means compare to the column value.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
Your coalesce is wrong. Try this
WHERE S.StudentId = COALESCE(@StudentId,S.StudentId)
what you were doing is saying when (@StudentId use null, what you really wanted to say was when (@StudentId is null don't compare - which effectively means compare to the column value.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
Thanks BOB....its working fine....and ur description is very clear..i mean short and sweet... thanks.... :)
dipak
-
Thanks BOB....its working fine....and ur description is very clear..i mean short and sweet... thanks.... :)
dipak
-
Your coalesce is wrong. Try this
WHERE S.StudentId = COALESCE(@StudentId,S.StudentId)
what you were doing is saying when (@StudentId use null, what you really wanted to say was when (@StudentId is null don't compare - which effectively means compare to the column value.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
I use the following
where (IsNull(@StudentID,0) = 0 OR StudentID = @StudentID)
and have done for years, I wonder if there is a benifit to using COALESCE. It is certainly more elegant than using the OR method.
Never underestimate the power of human stupidity RAH
-
I use the following
where (IsNull(@StudentID,0) = 0 OR StudentID = @StudentID)
and have done for years, I wonder if there is a benifit to using COALESCE. It is certainly more elegant than using the OR method.
Never underestimate the power of human stupidity RAH
For years I used the same sort of thing, then I went to
where IsNull(@StudentID,StudentID ) = StudentID
but apparently COALESCE is preferable to isnull - not sure why in this instance. Its certainly good where you have several possible values, such as
select coalesce(val1,val2,val3) as mainvalue
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP