Member Avatar for Ihab Salamin

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 because: 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
  • 102 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by padtes
Member Avatar for sknake

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))
Member Avatar for padtes

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))
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.