Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    38

    Unanswered: number of rows in a select stmt

    hey all,

    I am writing a sproc:

    select @strCourseNameRegFor = sal.CourseName , @strSectionNoRegFor = sal.SectionNo,
    @dteStartDateRegFor = sal.StartDate, @dteEndDateRegFor = sal.EndDate,
    @dteStartTimeRegFor = sal.StartTime, @dteEndTimeRegFor = sal.EndTime,
    @strDaysOfWeekRegFor = sal.DaysOfWeek
    from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
    where sal.SchoolYr = @intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@strCourseNamePrev))
    and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
    and sal.MaxNoStudents > sal.CurrNoStudents

    I want to check if the select returned an empty set or not. I cannot use @@rowcount because i am assigning the values to the local vars. I tried

    if @strCourseNameRegFor is null
    begin
    set @err = 'No courses';
    end

    but for some reason even if there are any records in the set, the if condition is getting satisfied. Can anyone help?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This means that sal.CourseName has a value of NULL when the WHERE clause is satisfied.

    But I don't understand why you can't use @@ROWCOUNT when assigning values to local variable.

    The following code produces a value of 1 for @@ROWCOUNT:

    use pubs
    declare @str char(12)
    select @str = au_id from authors where au_lname = 'smith'
    select @str, @@rowcount
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jan 2004
    Posts
    38

    Angry

    Thanks for the reply. I tried it in Query Analyzer and it prints out the value of @@rowcount but when I do the same thing in a sproc, it prints out 0 for @@rowcount even though the select returns 1 record. Heres my query in the sproc:

    select @strCourseNameRegFor = sal.CourseName , @strSectionNoRegFor = sal.SectionNo,
    @dteStartDateRegFor = sal.StartDate, @dteEndDateRegFor = sal.EndDate,
    @dteStartTimeRegFor = sal.StartTime, @dteEndTimeRegFor = sal.EndTime,
    @strDaysOfWeekRegFor = sal.DaysOfWeek
    from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
    where sal.SchoolYr = @intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@strCourseNamePrev))
    and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
    and sal.AvailOnLine = 1
    print @@rowcount;

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The following will produce a value of 9 for @@ROWCOUNT:

    use pubs
    declare @str varchar(25)
    select @str = au_id from authors where au_lname like '%s%'
    select @str, @@rowcount
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •