What is the benefit of using the SQL stored procedure?
■ The SQL stored procedure is much better than SQL command text. When a SQL statement is included in the stored procedure, the server does not have to analyze and compile it every time it executes it.
■ Call the stored procedure, it can be considered a three-layer structure. This makes your program easy to maintain. If the program needs to do some changes, you can change the stored procedure.
■ You can use Transact-SQL's powerful features during storage. A SQL stored procedure can contain multiple SQL statements. You can use variables and conditions. This means you can build a very complex query with a stored procedure to update the database in a very complex manner.
■ Finally, this may be the most important, and parameters can be used during the stored procedure. You can transfer and return parameters. You can also get a return value (from the SQL RETURN statement).
Environment: WinXP VB6 SP6 SQLSERVER2000
Database: test
Table: Users
Create Table [DBO]. [User]
[ID] [INT] Identity (1, 1) Not NULL,
[truename] [char] (10) collate chinese_prc_ci_as null,
[regName] [25) collate Chinese_prc_ci_as null,
[PWD] [25) Collate Chinese_PRC_CI_AS NULL,
[sex] [char] (10) collate chinese_prc_ci_as null,
[email] [text] collate chinese_prc_ci_as null,
[jifen] [decimal] (18, 2) NULL
) On [primary] textImage_on [primary]
Go
Alter Table [DBO]. [Users] with nocheck add
ConsTRAINT [PK_USERS] Primary Key Clustered
(
[ID]
) On [primary]
Go
Storage procedure SELECT_USERS
Create Procedure SELECT_USERS @Regname Char (20), @numrows Int Output
AS
SELECT * FROM USERS
SELECT @numrows = @@ RowCount
IF @numrows = 0
Return 0
ELSE RETURN 1
Go
Storage process insert_users
Create Procedure Insert_Users @trueename Char (20), @Regname Char (20), @ sex char (20), @ Email Char (20), @ Jifen Decimal (19, 2)
AS
INSERT INTO USERS (TrueName, Regname, PWD, SEX, Email, Jifen) Values (@ Truename, @ regname, @ pwd, @ sex, @ email, @ jifen)
Go
In the VB environment, add a DataGrid control, 4 buttons, 6 text boxes
The code is easy to understand.
'Quote Microsoft Active Data Object 2.x Library
Option expedition
DIM MCONN As Adodb.Connection
DIM RS1 As Adodb.Recordset
DIM RS2 As Adodb.Recordset
DIM RS3 As Adodb.Recordset
DIM RS4 as adodb.recordsetdim cmd as adoDb.command
Dim param as adodb.parameter
'Add data using the stored procedure with the first method.
Private submmand1_click ()
Set cmd = new adoDb.command
SET RS1 = New AdoDb.Recordset
cmd.activeConnection = MCONN
cmd.commandtext = "Insert_Users"
cmd.commandtype = adcmdstoredProc
Set param = cmd.createParameter ("Trueename", Adchar, Adparaminput, 20, TRIM (TXTTRUENAME.TEXT))
cmd.parameters.Append param
Set param = cmd.createParameter ("regName", Adchar, Adparaminput, 20, Trim (TXTREGNAME.TEXT))
cmd.parameters.Append param
Set param = cmd.createParameter ("PWD", Adchar, Adparaminput, 20, Trim (txtpwd.text))
cmd.parameters.Append param
Set param = cmd.createParameter ("SEX", Adchar, Adparaminput, 20, Trim (TXTSEX.TEXT))
cmd.parameters.Append param
Set param = cmd.createparameter ("email", adchar, adparaminput, 20, trim (txtemail.text))
cmd.parameters.Append param
'The following types need to be aware that if you do not use Adsingle, a precision invalid error occurs.
Set param = cmd.createParameter ("JIFEN", Adsingle, AdParaminput, 50, Val (txtjifen.text))
cmd.parameters.Append param
SET RS1 = cmd.execute
SET cmd = Nothing
SET RS1 = Nothing
End Sub
'Add data using stored procedures with the second method
Private sub fascist2_click ()
SET RS2 = New AdoDb.Recordset
Set cmd = new adoDb.command
cmd.activeConnection = MCONN
cmd.commandtext = "Insert_Users"
cmd.commandtype = adcmdstoredProc
cmd.parameters ("@ Trueename") = trim (txttrumename.text)
cmd.parameters ("@ regname") = trim (txtRegname.text)
cmd.parameters ("@PWD") = trim (txtpwd.text)
cmd.parameters ("@ sex") = trim (txtsex.text)
cmd.parameters ("@ email") = trim (txtemail.text) cmd.parameters ("@ jifen") = val (txtjifen.text)
SET RS2 = cmd.execute
SET cmd = Nothing
SET RS1 = Nothing
End Sub
'Use the third method to use the connection object to insert data
Private sub fascist4_click ()
DIM STRSQL AS STRING
Strsql = "INSERT_USERS '" & TRIM (TXTTRUENAME.TEXT) & "', '" & TRIM (TXTREGNAME.TEXT) & "'" "& TRIM (TXTPWD.TEXT) &" ',' "& TRIM (TXTSEX. Text) & "','" & TXTEMAIL.TEXT) & "','" & Val (txtjifen.text) & "'"
SET RS3 = New Adodb.Recordset
SET RS3 = MCONN.EXECUTE (STRSQL)
SET RS3 = Nothing
End Sub
'Display data using stored procedures
'To handle multiple parameters, enter parameters, output parameters, and a direct return value
Private submmand3_click ()
SET RS4 = New AdoDb.Recordset
Set cmd = new adoDb.command
cmd.activeConnection = MCONN
cmd.comMandtext = "SELECT_USERS"
cmd.commandtype = adcmdstoredProc
'return value
Set param = cmd.createParameter ("RetVal", Adinteger, AdParamReturnValue, 4)
cmd.parameters.Append param
'Input parameters
Set param = cmd.createParameter ("regName", Adchar, Adparaminput, 20, Trim (TXTREGNAME.TEXT))
cmd.parameters.Append param
'Output parameters
Set param = cmd.createParameter ("Numrows", Adinteger, ADPARAMOUTPUT)
cmd.parameters.Append param
SET RS4 = cmd.execute ()
IF cmd.parameters ("Retval"). Value = 1 THEN
Msgbox cmd.parameters ("nuMrows"). Value
Else
Msgbox "No Record"
END IF
Msgbox rs 4.Recordcount
SET DATAGRID1.DATASOSOURCE = RS4
DataGrid1.refresh
End Sub
'Connect to the database
Private sub flow_load ()
Set mconn = new connection
Mconn.connectionstring = "provider = SQLOLEDB.1; PERSIST security info = false; user ID = sa; initial catalog = test; data source = yang"
Mconn.cursorLocation = aduseclient 'is set to the client
Mconn.open
End Sub
'Close data connection
Private Sub Form_Unload (Cancel AS Integer)
Mconn.close
Set mconn = Nothing
End Sub
