By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,812 Members | 875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,812 IT Pros & Developers. It's quick & easy.

Database count statement problem

P: 67
i have a store procedure i am trying to count the records but getting the probelm with this please if any one know about this help me
my store procedure is like this

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE proc usp_employeenominations
  3.  
  4.            @NomineeFirstName  varchar (50)= null,
  5.         @NomineeLastName  varchar(50)=null,
  6.     @NomineeDept varchar(50) = null,
  7.     @Month varchar(50) = null,
  8.     @Year int = null
  9.  
  10.  
  11. )
  12. as
  13. begin
  14.  
  15. select count(*)As NominationCount,reason.employeenominationid,reason.NomineeLastName,reason.NomineeFirstName,EmployeeNominations.NomineeDept,reason.reasonid,EmployeeSelected=Case reason.EmployeeSelected
  16.  
  17. WHEN '1' THEN 'winner' --DATENAME(month,reason.Datecreated) +' '+'Winner'
  18.  
  19. WHEN '0' THEN 'Not Winner'
  20.  
  21. end
  22.  
  23. FROM dbo.Reason INNER JOIN
  24.  
  25. dbo.EmployeeNominations ON dbo.Reason.Employeenominationid = dbo.EmployeeNominations.EmployeeNominationid
  26.  
  27. where
  28.  
  29. Month(dbo.reason.DateCreated) = case @Month when null then Month(dbo.reason.DateCreated) else @Month end and
  30.  
  31. Year(dbo.reason.DateCreated) = case @Year when null then Year(dbo.reason.DateCreated) else @Year end and
  32.  
  33. datepart(m,dbo.reason.datecreated)=@month
  34.  
  35. group by reason.Nomineelastname, reason.NomineeFirstName,Reason.EmployeeSelected,EmployeeNominations.NomineeDept,reason.reasonid,reason.employeenominationid
  36. end
  37.  
Sep 17 '07 #1
Share this Question
Share on Google+
1 Reply


Plater
Expert 5K+
P: 7,872
I believe the problem is you are trying to return a scaler value in with record data.
That's a no-no and has to be done seperately.
You could simple request the data and then count the number of records after.
(Actually if you populate a DataTable it's ridiculously easy because it's done for you already)
Sep 17 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.