By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Stored procedure where clause

P: n/a
I have an existing query from MS Access that I want to convert it to
SQL Server Stored Proc. My problem is on how to convert the WHERE
clause.

This is the query from MS Access:

SELECT SchYrSemCourseJoin.SchYrSemCourseID, Students.IDNo, [LastName]
& ", " & [FirstName] & " " & [MiddleName] AS Name,
Program.ProgramTitle, Program.ProgramDesc, SchYrSem.SchYr,
SchYrSem.Sem, SchYrSem.Year, SchYrSem.Section AS Section1,
Major.Major, Course.CourseCode, Course.CourseTitle, Course.Unit,
SchYrSemCourseJoin.Final, SchYrSem.SchYrSemID
FROM (Program INNER JOIN Students ON Program.ProgramID =
Students.ProgramID) INNER JOIN ((Major INNER JOIN SchYrSem ON
Major.MajorID = SchYrSem.MajorID) INNER JOIN (Course INNER JOIN
SchYrSemCourseJoin ON Course.CourseID = SchYrSemCourseJoin.CourseID)
ON SchYrSem.SchYrSemID = SchYrSemCourseJoin.SchYrSemID) ON
Students.IDNo = SchYrSem.IDNo
WHERE ((([LastName] & ", " & [FirstName] & " " &
[MiddleName])=[Forms]![Rating Report Dialog]![SubName]) AND
((SchYrSem.Year) Like IIf(IsNull([Enter Value]),"*",[Enter Value])));

This is a stored proc that I have currently created:

CREATE PROCEDURE dbo.Rating
@LastName nvarchar(50)
AS SELECT SchYrSemCourseJoin.SchYrSemCourseID, Students.IDNo,
[LastName] + ', ' + [FirstName] + ' ' + [MiddleName] AS Name,
Program.ProgramTitle, Program.ProgramDesc, SchYrSem.SchYr,
SchYrSem.Sem, SchYrSem.Year, SchYrSem.Section AS Section1,
Major.Major, Course.CourseCode, Course.CourseTitle, Course.Unit,
SchYrSemCourseJoin.Final, SchYrSem.SchYrSemID
FROM (Program INNER JOIN Students ON Program.ProgramID =
Students.ProgramID) INNER JOIN ((Major INNER JOIN SchYrSem ON
Major.MajorID = SchYrSem.MajorID) INNER JOIN (Course INNER JOIN
SchYrSemCourseJoin ON Course.CourseID = SchYrSemCourseJoin.CourseID)
ON SchYrSem.SchYrSemID = SchYrSemCourseJoin.SchYrSemID) ON
Students.IDNo = SchYrSem.IDNo
WHERE ((([LastName] + ', ' + [FirstName] + ' ' +
[MiddleName])=@LastName)) Return
GO

My problem is on how can I add the second criteria which is the Field
Year on my stored proc. The query above (MS Access) returns all the
records if the Parameter Enter Value is null.

Anyone know how to do this in stored proc? I want to create a stored
proc that will have the same results as the query above.

Thanks in advance.
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Try this:
And (SchYrSem.Year) Like IsNull(@SchYr, SchYrSem.Year) + '%'

Jul 23 '05 #2

P: n/a
On Thu, 20 Jan 2005 10:49:50 +0800, jaYPee wrote:

(snip)
AND
((SchYrSem.Year) Like IIf(IsNull([Enter Value]),"*",[Enter Value]))); (snip)Anyone know how to do this in stored proc? I want to create a stored
proc that will have the same results as the query above.


Hi jaYPee,

if year is a character column, you could do a fairly straight translation:
WHERE SchYrSem.Year LIKE COALESCE(@SchYr, '%')

This is not a good solution if Year is a numeric datatype (which it should
be). The following will work for all datatypes:
WHERE SchYrSem.Year = COALESCE (@SchYr, SchYrSem.Year)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.