Stored procedure where clause

[Guest]

You're probably best posting this in a different discussion group as this
one is primarily for VB.Net, not SQL or Access. You should get more replies
if you do that.

 

[JohnFol]

Your query needs another parameter to accept the "[Enter Value]" value.
Then within whatever environment you are using you need to capture it in
exactly the same way as you capture @LastName

 

[JohnFol]

WHERE [LastName] + ', ' + [FirstName] + ' ' + [MiddleName]=@LastName And
(SchYrSem.Year = @Year or @Year Is Null)

Yah. but how can I make a parameter to return all records if the
[Enter Value] Parameter as in MS Access returns null.

In my stored proc I can use:

WHERE [LastName] + ', ' + [FirstName] + ' ' +
[MiddleName]=@LastName And SchYrSem.Year = @Year

But what I the value of @Year is null? In this case if the value of
@Year is null I want to return all records as if there is no criteria
for SchYrSem.Year Field.

Your query needs another parameter to accept the "[Enter Value]" value.
Then within whatever environment you are using you need to capture it in
exactly the same way as you capture @LastName

 

[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 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.

 

[jaYPee]

I tried your code but if I don't supply a value for @Year parameter it
does not return any records.

WHERE [LastName] + ', ' + [FirstName] + ' ' + [MiddleName]=@LastName And
(SchYrSem.Year = @Year or @Year Is Null)

Yah. but how can I make a parameter to return all records if the
[Enter Value] Parameter as in MS Access returns null.

In my stored proc I can use:

WHERE [LastName] + ', ' + [FirstName] + ' ' +
[MiddleName]=@LastName And SchYrSem.Year = @Year

But what I the value of @Year is null? In this case if the value of
@Year is null I want to return all records as if there is no criteria
for SchYrSem.Year Field.

Your query needs another parameter to accept the "[Enter Value]" value.
Then within whatever environment you are using you need to capture it in
exactly the same way as you capture @LastName

 

[jaYPee]

Yah. but how can I make a parameter to return all records if the
[Enter Value] Parameter as in MS Access returns null.

In my stored proc I can use:

WHERE [LastName] + ', ' + [FirstName] + ' ' +
[MiddleName]=@LastName And SchYrSem.Year = @Year

But what I the value of @Year is null? In this case if the value of
@Year is null I want to return all records as if there is no criteria
for SchYrSem.Year Field.