Stored Proc that does multiple counts and passes back multiple out put params

• me-857278

  SSC Veteran

  Points: 284

  More actions

  September 10, 2008 at 7:34 pm

  #197930

  HI,

  I have to do some reporting and there are many things to count on the backend and report to the aspx page..

  I always use stored procedures.

  for instance

  CREATE PROCEDURE [dbo].[sp_CountHeavy]

  @Count as integer output

  As

  SELECT @Count = count( *) from exams where score between 36 and 52

  However there are many things to count and it would be better if I could do them all in 1 SP ..

  like this: but I know I have the syntax wrong...

  CREATE PROCEDURE [dbo].[sp_CountAll]

  @CountHigh as integer output,

  @CountMed as integer output,

  @CountLow as integer output

  As

  SELECT @CountHigh = count( *) from exams where score between 36 and 52

  SELECT @CountMed = count( *) from exams where score between 16 and 35

  SELECT @CountLow = count( *) from exams where score between 1 and 15

  I know I am missing something... but what? I can do this with 3 procs but it would be better to combine into 1 if it is possible!

  Thanks!

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  September 10, 2008 at 7:57 pm

  #870075

  I think that you want something like this:

  Select @CountHigh = SUM(Case When score Between 36 and 52 Then 1 Else 0 End)

  , @CountHigh = SUM(Case When score Between 16 and 35 Then 1 Else 0 End)

  , @CountHigh = SUM(Case When score Between 1 and 15 Then 1 Else 0 End)

  From exams

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• me-857278

  SSC Veteran

  Points: 284

  More actions

  September 10, 2008 at 8:03 pm

  #870077

  What if they were different tables?

  CREATE PROCEDURE [dbo].[sp_CountAll]

  @CountHigh as integer output,

  @CountExams as integer output,

  @CountStudents as integer output

  As

  SELECT @CountHigh = count( *) from exams where score between 36 and 52

  SELECT @CountExams = count( *) from exams

  SELECT @CountStudents = count( *) from Students

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  September 10, 2008 at 8:21 pm

  #870085

  Counts on different tables should be in different queries.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• me-857278

  SSC Veteran

  Points: 284

  More actions

  September 10, 2008 at 9:24 pm

  #870096

  OK, Thanks!

  Just one more.. Can this be done? I have to count each for 20 different REFID's

  CREATE PROCEDURE [dbo].[sp_CountByRefID]

  @CountA as integer output,

  @CountB as integer output,

  @CountC as integer output,

  @CountD as integer output

  As

  SELECT @CountA = count( *) from Entries where REFID='A'

  SELECT @CountB = count( *) from Entries where REFID='B'

  SELECT @CountC = count( *) from Entries where REFID='C'

  SELECT @CountD = count( *) from Entries where REFID='D'

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  September 11, 2008 at 7:59 am

  #870313

  Yes, just apply the same technique that I did in my first reply.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• me-857278

  SSC Veteran

  Points: 284

  More actions

  September 11, 2008 at 8:34 am

  #870356

  Thanks!