Selecting columns from a stored procedure

Question

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

Vincent Maverick Durano · Answer

First you need to modify your stored procedure to accept parameters such as student no or date then you can pass the values to it like this: protected void Button1_Click(object sender EventArgs e) { DataTable dt = new DataTable(); using(SqlConnection connection = new SqlConnection(Your Connection String)){ connection.Open(); string spName = YOURStoredProcudureName; using(SqlCommand sqlCmd = new SqlCommand(spName connection)){ sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue(@StudentNoTextBox1.Text.Trim()); sqlCmd.Parameters.AddWithValue(@SelectedDateCalendar1.SelectedDate); using(SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd)){ sqlDa.Fill(dt); if (dt.Rows.Count > 0) { //Get the data from DataTable string hoursPresent = dt.Rows[0][NO_HRS_PRESENT].ToString(); string hoursAbsent= dt.Rows[0][NO_HRS_ABSNT].ToString(); } } } } } NOTE: @StudentNo and @DateSelected should be the exact same name of your parameter from your SP.

Benjamin Allen · Answer

This is really helpful Mr. Vincent but I want to include a where clause in the select query so that a user can enter either student regno in a textbox or date(from and to) which also appeared in a textbox when user select calendar date on the web form so that only record for that specified student or date is displayed on a gridview. Thanks

Vincent Maverick Durano · Answer

You could easily get your data from your SP using the the traditional ADO.NET way. For example: protected void Button1_Click(object sender EventArgs e) { DataTable dt = new DataTable(); using(SqlConnection connection = new SqlConnection(Your Connection String)){ connection.Open(); string spName = YOURStoredProcudureName; using(SqlCommand sqlCmd = new SqlCommand(spName connection)){ sqlCmd.CommandType = CommandType.StoredProcedure; using(SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd)){ sqlDa.Fill(dt); if (dt.Rows.Count > 0) { //Get the data from DataTable string hoursPresent = dt.Rows[0][NO_HRS_PRESENT].ToString(); string hoursAbsent= dt.Rows[0][NO_HRS_ABSNT].ToString(); } } } } }

Manas Mohapatra · Answer

It is there in your Stored Procedure. Select * ISNULL(NO_HRS_PRESENT0)*100/ ((ISNULL(NO_HRS_PRESENT0))+(ISNULL(NO_HRS_ABSNT0))) PRSNT_PERC ISNULL(NO_HRS_ABSNT0)*100/ ((ISNULL(NO_HRS_PRESENT0))+(ISNULL(NO_HRS_ABSNT0))) ABSNT_PERC Here * represents REGNO FIRSTNAME LASTNAME NO_HRS_PRESENT NO_HRS_ABSNT columns. And also you have added two extra columns PRSNT_PERC and ABSNT_PERC. You can fetch in code like: SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { Console.WriteLine(reader[NO_HRS_PRESENT])); } }

Benjamin Allen · Answer

Manas the values of the columns comes from a column named status(P for present and A for absent) in my database table. The COUNT of P is where I got the totalpresent column and from there I calculated the percentage as seen in the stored procedure. Thanks

Benjamin Allen · Answer

Thanks Amit but sorry I don't understand it very well. You mean I should create a new stored procedure that the one I had already created? It will be better I you can give me one short example. Thanks once more

Manas Mohapatra · Answer

What will be the value of two temporary columns totalhourabsent and totalhourpresent. Means where you will get the value of two above columns.

Amit Singh · Answer

1.Create Procedure ( Variable Declaration @Opt varchar(2) ) AS BEGIN if(@Opt='S') BEGIN // Your Code Goes Here END END 2. 1.First Create one Class In Class Create using dataset crate one method 2.Pass all parameters including @opt For Example public static dataset Dbconnect(string FirstNamestring Opt) { Connection goes here } 3.On Button Click Dataset ds=null; ds=classname.MethodName('''''''S') Note: if string value is there just put :- '' if integer values is there just put :- 0

Benjamin Allen · Answer

Thanks Vincent for the reply. Selecting them from the stored procedure isn't what is giving me problem but select those required columns from the already created stored procedure under a button event is what is giving problem as the columns aren't on the database table. Or let me put is this way I want to execute the stored procedure from my code behind under a button event

Vincent Maverick Durano · Answer

You have to include those columns in your select query so you could use them in your code.