Stored Procedure is a program stored in the data base as well as data. It is actually quite unusual, because we expect the stored data in the development of data base is not his program.
The core competency is in SQL Server Store Procedures and Functions (Functions only found in SQL Server 2000).
With the Store Procedure, then the SQL program that we created:
The core competency is in SQL Server Store Procedures and Functions (Functions only found in SQL Server 2000).
With the Store Procedure, then the SQL program that we created:
- can be used anytime.
- more quickly and efficiently because it is Server Side.
- easily created and maintained by a small but 'Power Full'.
Strored Making Procedure.
Syntax:
CREATE PROCEDURE nama_procedure
[@ Parameter1 tipe_data_parameter1, ... ... ... .., [@ ParameterNtipe_data_parameterN]
U.S.
SQL or TSQL statement
TSQL is the development of SQL, thus becoming more Powerful.
The following store procedure creation through the Enterprise Manager:
- Click Stored Procedure in Database Northwind.
- Right click on Area Name.
- Select New Stored Procedure.
Change the statement that there is a Stored Procedure the following:
Dim Conn As New ADODB.Connection
Dim rsTransaksi As New ADODB.Recordset
Dim StrSQL As String, OpenConnecting As String
Private Sub Form_Load()
Dim CustomerId As String
CustomerId = InputBox("Please Input Customer ID !")
OpenConnecting = "Provider=SQLOLEDB.1;Persist Security Info=False;" _
& "User ID=sa;Initial Catalog=NorthWind;Data Source=(local)"
If Conn.State = adStateOpen Then
Conn.Close
Set Conn = New ADODB.Connection
Conn.Open OpenConnecting
Else
Conn.Open OpenConnecting
End If
rsTransaksi.ActiveConnection = Conn
rsTransaksi.CursorLocation = adUseClient
rsTransaksi.CursorType = adOpenDynamic
rsTransaksi.LockType = adLockOptimistic
rsTransaksi.Open "EXEC CustOrderHist '" & CustomerId & "'"
Set DataGrid1.DataSource = rsTransaksi
End Sub
The result:
Then To display the data into a form using a stored procedure, create a form like the following picture:
The following program code:
Dim rsTransaksi As New ADODB.Recordset
Dim StrSQL As String, OpenConnecting As String
Private Sub Form_Load()
Dim CustomerId As String
CustomerId = InputBox("Please Input Customer ID !")
OpenConnecting = "Provider=SQLOLEDB.1;Persist Security Info=False;" _
& "User ID=sa;Initial Catalog=NorthWind;Data Source=(local)"
If Conn.State = adStateOpen Then
Conn.Close
Set Conn = New ADODB.Connection
Conn.Open OpenConnecting
Else
Conn.Open OpenConnecting
End If
rsTransaksi.ActiveConnection = Conn
rsTransaksi.CursorLocation = adUseClient
rsTransaksi.CursorType = adOpenDynamic
rsTransaksi.LockType = adLockOptimistic
rsTransaksi.Open "EXEC CustOrderHist '" & CustomerId & "'"
Set DataGrid1.DataSource = rsTransaksi
End Sub
The result:
