0

i have the following Query , i built it in sql server 2008 query builder

(SELECT     CourseNo
FROM         ppu_RegistrationInfoDetailes
WHERE     (StudentNo = @StudentNo) AND (SemesterNo =
                          (SELECT     TOP (1) SemesterNo
                            FROM          ppu_RegistrationInfo AS ppu_RegistrationInfo_1
                            WHERE      (StudentNo = @StudentNo)
                            ORDER BY AcademicYear DESC)) AND (AcademicYear =
                          (SELECT     TOP (1) AcademicYear
                            FROM          ppu_RegistrationInfo AS ppu_RegistrationInfo_1
                            WHERE      (StudentNo = @StudentNo)
                            ORDER BY AcademicYear DESC)))

This query will return the Course number of the registered course for the current semester
when i execute it , in query builder it execute correctly

when i create a stored producer for this query no rows affected
this is my stored producer

(ALTER PROCEDURE CurrentSemesterCourses
   
    @StudentNo int
   
AS
    DECLARE @Semester int
    DECLARE @AcademicYear int
   
   
        SET @Semester = (SELECT     TOP (1) SemesterNo
FROM         ppu_RegistrationInfo AS ppu_RegistrationInfo_1
WHERE     (StudentNo = @StudentNo)
ORDER BY AcademicYear DESC)
   
    Set @AcademicYear =(SELECT     TOP (1) AcademicYear
FROM         ppu_RegistrationInfo AS ppu_RegistrationInfo_1
WHERE     (StudentNo = @StudentNo)
ORDER BY AcademicYear DESC)


SELECT     CourseNo
FROM         ppu_RegistrationInfoDetailes
WHERE     (StudentNo = @StudentNo) AND (SemesterNo =@Semester) AND (AcademicYear =@AcademicYear))

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

3
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by padtes
0

Add print lines to see if your values are being set properly for @Semester and @AcademicYear

See what this turns up:

Declare @StudentNo int
Set @StudentNo = 12345
--

DECLARE @Semester int
DECLARE @AcademicYear int


SET @Semester = (SELECT TOP (1) SemesterNo
FROM ppu_RegistrationInfo AS ppu_RegistrationInfo_1
WHERE (StudentNo = @StudentNo)
ORDER BY AcademicYear DESC)

Set @AcademicYear =(SELECT TOP (1) AcademicYear
FROM ppu_RegistrationInfo AS ppu_RegistrationInfo_1
WHERE (StudentNo = @StudentNo)
ORDER BY AcademicYear DESC)


Print Cast(@Semester as varchar)
Print Cast(@AcademicYear as varchar)
SELECT CourseNo
FROM ppu_RegistrationInfoDetailes
WHERE (StudentNo = @StudentNo) AND (SemesterNo =@Semester) AND (AcademicYear =@AcademicYear))
0

In addition to Scott Knake's suggestion, I also see logical problem. First you need to find year then semester at that year

ALTER PROCEDURE CurrentSemesterCourses

@StudentNo int

AS
DECLARE @Semester int
DECLARE @AcademicYear int

Set @AcademicYear =(SELECT TOP (1) AcademicYear
FROM ppu_RegistrationInfo AS ppu_RegistrationInfo_1
WHERE (StudentNo = @StudentNo)
ORDER BY AcademicYear DESC)

SET @Semester = (SELECT TOP (1) SemesterNo
FROM ppu_RegistrationInfo AS ppu_RegistrationInfo_1
WHERE (StudentNo = @StudentNo) 
AND AcademicYear = @AcademicYear
ORDER BY SemesterNo DESC)



SELECT CourseNo
FROM ppu_RegistrationInfoDetailes
WHERE (StudentNo = @StudentNo) AND (SemesterNo =@Semester) AND (AcademicYear =@AcademicYear))
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.