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

Number of duplicate record

I need help writing a query that can tell me the number of records with the
same content.
For example, this is my database (Data1 only has 1 column)
Table Data1
Column numbers
234
322
2323
234
453
234
412
2323
the query should like something like this
Select ..... From Data1 Where ... numbers =..
if i use
Select ..... From Data1 Where ... numbers = 234
it would return 3, because there are 3 234s in the column
Thanks in Advance,
AaronHi Aaron,
You could try
Select count(*) from Data1 Where Numbers = 234
HTH
Barry|||Select count(*) From Data1 Where numbers = 234
"Aaron" <kuya789@.yahoo.com> wrote in message
news:%23$tXt9cHFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I need help writing a query that can tell me the number of records with
the
> same content.
> For example, this is my database (Data1 only has 1 column)
> Table Data1
> Column numbers
> 234
> 322
> 2323
> 234
> 453
> 234
> 412
> 2323
> --
> the query should like something like this
> Select ..... From Data1 Where ... numbers =..
> if i use
> Select ..... From Data1 Where ... numbers = 234
> it would return 3, because there are 3 234s in the column
>
> Thanks in Advance,
> Aaron
>|||Aaron,
If you want count the duplicate Records for the whole table rather than
individually then use:
select count(*), Numbers
>From Data1
Group By Numbers
Having Count(Numbers) > 1
HTH
Barry|||thanks.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1109683340.054164.176760@.g14g2000cwa.googlegroups.com...
> Aaron,
> If you want count the duplicate Records for the whole table rather than
> individually then use:
> select count(*), Numbers
> Group By Numbers
> Having Count(Numbers) > 1
> HTH
> Barry
>

NULL to Zero

Is there any function that would change a NULL value to zero? I am writing a
view that need to calculate the net inventory by subtracting the export qty
from import qty. However, in case of no export, 1-Null=Null. Thanks.[posted and mailed, please reply in news]

John Q (johnq@.hkayp.org) writes:
> Is there any function that would change a NULL value to zero? I am
> writing a view that need to calculate the net inventory by subtracting
> the export qty from import qty. However, in case of no export,
> 1-Null=Null. Thanks.

coalesce(val1, val2, ..., valn)

returns the first non-NULL value in the list.

(There is also a function isnull() which has a clearer name, but is
restricted to two parameters only. coalesce() is from the ANSI standard.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Thank you very much. This function is very useful to me.

Best Regards,
Frederick

"Erland Sommarskog" <sommar@.algonet.se> bl
news:Xns93EC5BCD76E8Yazorman@.127.0.0.1 g...
> [posted and mailed, please reply in news]
> John Q (johnq@.hkayp.org) writes:
> > Is there any function that would change a NULL value to zero? I am
> > writing a view that need to calculate the net inventory by subtracting
> > the export qty from import qty. However, in case of no export,
> > 1-Null=Null. Thanks.
> coalesce(val1, val2, ..., valn)
> returns the first non-NULL value in the list.
> (There is also a function isnull() which has a clearer name, but is
> restricted to two parameters only. coalesce() is from the ANSI standard.)
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp