Below is the code to create Stored Procedure in SQL Server
procedure is created with name "ActiveFaculties"
------------------------------------------------------------
CREATE PROCEDURE dbo.ActiveFaculties
@Status nchar(10)
AS
SELECT ID, Name
FROM Faculty WHERE Status=@Status
Return
Below is the code to diaplaying how to use Stored Procedure to get Data
A page contains one Panel control in which data will be added....
-------------------------------------------------------------------------
Imports System.Data.SqlClient
Partial Class Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Call_Stored_Proc()
End Sub
Protected Sub Call_Stored_Proc()
Dim Cn As SqlConnection
Dim Cmd As SqlCommand
Dim Dr As SqlDataReader
Cn = New SqlConnection("Data Source=ServerName\your database;Initial Catalog=Attendance;Integrated Security=True")
Cn.Open()
Try
With con
Cmd = New SqlCommand("ActiveFaculties", Cn)
Cmd.CommandType = Data.CommandType.StoredProcedure
'Passing parameter to the argument to get specific data...
Cmd.Parameters.Add("@Status", Data.SqlDbType.Char).Value = "INACTIVE"
Dr = Cmd.ExecuteReader
If Dr.HasRows Then
While Dr.Read
'Add User's ID and Name in Panel1
Panel1.Controls.Add(New LiteralControl(Dr("ID") & vbTab & Dr("Name") & "
"))
End While
End If
Dr.Close()
End With
Catch ex As Exception
Response.Write(ex.Message)'Shows Error , If any
Finally
cn.close
End Try
End Sub
End Class
---------------------------------------------------------------------------------------------
I hope above code will help you in designing Stored Procedure to retrieve data from the table
I am always open to solve query.....
