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?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|||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;|||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