Stored procedure where clause

[jaYPee]

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 SchYrSemCourseJ oin.SchYrSemCou rseID, Students.IDNo, [LastName]
& ", " & [FirstName] & " " & [MiddleName] AS Name,
Program.Program Title, Program.Program Desc, SchYrSem.SchYr,
SchYrSem.Sem, SchYrSem.Year, SchYrSem.Sectio n AS Section1,
Major.Major, Course.CourseCo de, Course.CourseTi tle, Course.Unit,
SchYrSemCourseJ oin.Final, SchYrSem.SchYrS emID
FROM (Program INNER JOIN Students ON Program.Program ID =
Students.Progra mID) INNER JOIN ((Major INNER JOIN SchYrSem ON
Major.MajorID = SchYrSem.MajorI D) INNER JOIN (Course INNER JOIN
SchYrSemCourseJ oin ON Course.CourseID = SchYrSemCourseJ oin.CourseID)
ON SchYrSem.SchYrS emID = SchYrSemCourseJ oin.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 SchYrSemCourseJ oin.SchYrSemCou rseID, Students.IDNo,
[LastName] + ', ' + [FirstName] + ' ' + [MiddleName] AS Name,
Program.Program Title, Program.Program Desc, SchYrSem.SchYr,
SchYrSem.Sem, SchYrSem.Year, SchYrSem.Sectio n AS Section1,
Major.Major, Course.CourseCo de, Course.CourseTi tle, Course.Unit,
SchYrSemCourseJ oin.Final, SchYrSem.SchYrS emID
FROM (Program INNER JOIN Students ON Program.Program ID =
Students.Progra mID) INNER JOIN ((Major INNER JOIN SchYrSem ON
Major.MajorID = SchYrSem.MajorI D) INNER JOIN (Course INNER JOIN
SchYrSemCourseJ oin ON Course.CourseID = SchYrSemCourseJ oin.CourseID)
ON SchYrSem.SchYrS emID = SchYrSemCourseJ oin.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.

[maxl@msn.com]

Re: Stored procedure where clause

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

[Hugo Kornelis]

Re: Stored procedure where clause

On Thu, 20 Jan 2005 10:49:50 +0800, jaYPee wrote:

(snip)[color=blue]
> AND
>((SchYrSem.Yea r) Like IIf(IsNull([Enter Value]),"*",[Enter Value])));[/color]
(snip)[color=blue]
>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.[/color]

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)