February 13, 2009 at 6:12 pm
I am trying to create a function that will check if there are any future classes for a certain location based on locationID. (I know the syntax is incorrect). If there are future classes, I would like the function to return a 1 otherwise 0.
This is what I have so far:
CREATE FUNCTION uf_future_classes
(
@locationID int
)
RETURNS int
AS
BEGIN
DECLARE @counter int
SET @counter = select count(*)
FROM course_Schedule
WHERE locationID = @locationID and courseDate > getdate()
IF @counter > 0
RETURN 1
ELSE
RETURN 0
END
February 13, 2009 at 6:34 pm
you were so close on the syntax;
to assign to a static value, you usually use SET; but
you can assign a variable inside a select, like SELECT @CITY=CITY,@STATE=STE FROM ADDRESSES....
here's your function:
CREATE FUNCTION uf_future_classes
(
@locationID int
)
RETURNS int
AS
BEGIN
DECLARE @counter int
select @counter =count(*)
FROM course_Schedule
WHERE locationID = @locationID and courseDate > getdate()
IF @counter > 0
RETURN 1
ELSE
RETURN 0
END
Lowell
February 13, 2009 at 7:17 pm
Lowell,
Thank you very much for your assistance! The function works now.
I did forget to add a return variable. So if anyone is reading this, the complete correct Function is this:
CREATE FUNCTION uf_future_classes --uf_future_classes 15
(
@locationID int
)
RETURNS int
AS
BEGIN
DECLARE @counter int
DECLARE @future int
select @counter =count(*)
FROM course_Schedule
WHERE locationID = @locationID and courseDate > getdate()
IF @counter > 0
RETURN 1
ELSE
RETURN 0
Return @future
END
-Norbert
February 13, 2009 at 8:09 pm
There's no need for any local variables nor the flow control of IF in this function...
CREATE FUNCTION uf_future_classes --uf_future_classes 15
(
@LocationID INT
)
RETURNS INT
AS
BEGIN
RETURN (
SELECT SIGN(COUNT(*))
FROM Course_Schedule
WHERE LocationID = @LocationID
AND CourseDate > GETDATE()
)
END
--Jeff Moden
February 16, 2009 at 10:35 am
Jeff,
Wow. This is really efficient! Works great. Thanks for your help!
Norbert
February 16, 2009 at 10:53 am
Thanks for the feedback, Norbert. And, consider this... since the code is so simple, are you sure you want the system to "suffer" through the overhead associated with a UDF? Couldn't you put the code in a derived table to prevent the RBAR associated with a UDF?
--Jeff Moden
February 16, 2009 at 11:08 am
Jeff Moden (2/16/2009)
Thanks for the feedback, Norbert. And, consider this... since the code is so simple, are you sure you want the system to "suffer" through the overhead associated with a UDF? Couldn't you put the code in a derived table to prevent the RBAR associated with a UDF?
Hi Jeff, I am sorry. I don't know how to put it in a derrived table. I am using the UDF in a stored procedure right now.
February 16, 2009 at 11:12 am
Is it possible to post the sproc? I can give it a try for you.
--Jeff Moden
February 16, 2009 at 11:36 am
I think that maybe what Jeff meant.
SELECT l.Locationid, l.LocationName,
(SELECT SIGN(COUNT(*))
FROM Course_Schedule
WHERE LocationID = l.Locationid
AND CourseDate > GETDATE() ) futureclassind
FROM Location l
OR
SELECT l.Locationid, l.LocationDesc, i.futureclassind
FROM Location l
INNER JOIN (SELECT, Locationid, SIGN(COUNT(*)) futureclassind
FROM Course_Scheduel
WHERE CourseDate > GETDATE()
GROUP BY Locationid) i ON l.Locationid = i.Locationid
February 16, 2009 at 11:45 am
Jeff Moden (2/16/2009)
Is it possible to post the sproc? I can give it a try for you.
This is the stored proc. On my asp page I will be displaying information differently if there are future classes. dbo.uf_future_classes(Course_Schedule.LocationID) as futureYes
ALTER PROCEDURE [dbo].[usp_faculty_comments] --usp_faculty_comments 4
@facultyID int
AS
BEGIN
SET NOCOUNT ON;
SELECT Course_Schedule.FacultyID, dbo.uf_future_classes(Course_Schedule.LocationID) as futureYes, Location.Location_Name, Course_Schedule.CourseDate, Course_Schedule.Course_Number,
Faculty_Comments.CommentID, Faculty_Comments.Comment,
Faculty_Comments.Name, Course_Schedule.CourseScheduleID
FROM Course_Schedule INNER JOIN
Faculty_Comments
ON Course_Schedule.CourseScheduleID = Faculty_Comments.CourseScheduleID
INNER JOIN Location
ON Course_Schedule.LocationID = Location.LocationID
WHERE (Course_Schedule.FacultyID = @facultyID)
END
February 16, 2009 at 1:05 pm
Loner was on the right track... but, considering your query, not sure it's worth taking the time... you'd have to check for performance. Here's what your query would look like if the function were incorporated as a derived table. Obviously, I don't have your data, so I can't test for correct functionality nor performance...
SELECT Course_Schedule.FacultyID,
ISNULL(fy.FutureYes,0) AS FutureYes,
Location.Location_Name,
Course_Schedule.CourseDate,
Course_Schedule.Course_Number,
Faculty_Comments.CommentID,
Faculty_Comments.Comment,
Faculty_Comments.Name,
Course_Schedule.CourseScheduleID
FROM Course_Schedule
INNER JOIN Faculty_Comments ON Course_Schedule.CourseScheduleID = Faculty_Comments.CourseScheduleID
INNER JOIN Location ON Course_Schedule.LocationID = Location.LocationID
LEFT OUTER JOIN
(--==== Derived table finds future scheduled classes
SELECT LocationID, SIGN(COUNT(*)) AS FutureYes
FROM Course_Schedule
WHERE CourseDate > GETDATE()
GROUP BY LocationID
) fy
ON Course_Schedule.LocationID = fy.LocationID
WHERE Course_Schedule.FacultyID = @facultyID
I value performance almost above all else except... getting it right all the time is absolutely the most important. If it helps your developers in getting such code right all the time, then the use of a UDF is probably the more correct solution.
--Jeff Moden
February 16, 2009 at 2:31 pm
Since you don't care what the count is, but only that there IS a count, you might care to simply look at a "distinct" sub-query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 24, 2009 at 10:54 am
Jeff and Matt,
Thank you both for your help!
The data is quite small. Only a few thousand rows. No real perfomance issues. The previous version works perfectly!
Norbert
February 24, 2009 at 6:13 pm
norbertackerman (2/24/2009)
Jeff and Matt,Thank you both for your help!
The data is quite small. Only a few thousand rows. No real perfomance issues. The previous version works perfectly!
Norbert
You're welcome, but... saying the data is "only a few thousand rows" is an invitation to future disasters... and maybe not the way you may think. If you have code that works and someone gets in a hurry because of, say, an unreasonable schedule, it's human nature to copy working code. If it was designed with "only a few thousand rows" in mind, it could be a disaster if someone copies the code with "a few million rows" in mind. Heh... and how many people will document the code with a comment like "WARNING! THIS CODE WAS DESIGNED WITH ONLY A FEW THOUSAND ROWS IN MIND"? 😉
--Jeff Moden
February 24, 2009 at 6:51 pm
You're welcome, but... saying the data is "only a few thousand rows" is an invitation to future disasters... and maybe not the way you may think. If you have code that works and someone gets in a hurry because of, say, an unreasonable schedule, it's human nature to copy working code. If it was designed with "only a few thousand rows" in mind, it could be a disaster if someone copies the code with "a few million rows" in mind. Heh... and how many people will document the code with a comment like "WARNING! THIS CODE WAS DESIGNED WITH ONLY A FEW THOUSAND ROWS IN MIND"? 😉
Jeff - you make an excellent point. Thank you for your input!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply