Benjamin Allen

Benjamin Allen

  • NA
  • 34
  • 7.3k

Selecting columns from a stored procedure

Apr 25 2016 2:10 PM

Hi everyone

I have the below stored procedure and I want to select all columns to display them on the web form using "where clause" by specifying the "regno" provided by user in a textbook named txtboxsearch or "date from and to" by user selecting time on the displayed calender. The problem am having is how to select the column names like "totalhourabsent" and "totalhourpresent" that aren't in the database table but rather in the stored procedure.

 

Below is my stored procedure:

CREATE PROCEDURE [dbo].[GetAttendanceByHours]
 
AS
 
BEGIN

Select *,
ISNULL(NO_HRS_PRESENT,0)*100/ ((ISNULL(NO_HRS_PRESENT,0))+(ISNULL(NO_HRS_ABSNT,0))) PRSNT_PERC,
ISNULL(NO_HRS_ABSNT,0)*100/ ((ISNULL(NO_HRS_PRESENT,0))+(ISNULL(NO_HRS_ABSNT,0))) ABSNT_PERC
from
(
select
REGNO,
FIRSTNAME,
LASTNAME,
MAX(case when status = 'P' THEN CNT end) NO_HRS_PRESENT,
MAX(case WHEN STATUS = 'A' THEN CNT END) NO_HRS_ABSNT
from
(
select
REGNO,FIRSTNAME, LASTNAME,status,
count(status) CNT from AttendanceTable
group by regno,firstname, lastname, status
) A
GROUP BY regno,firstname, lastname
) tmp


END
 


Answers (10)