1. Call stored procedure, but no return value
Copy Code code as follows:
Private Function SqlProc1 (ByVal procname as String) as Boolean
' Defines the data link part omitted, myconn for the link object procname for the stored procedure name
Dim mycommand as New sqlclient.sqlcommand (procname, myconn)
With MyCommand
. CommandType = CommandType.StoredProcedure
. Parameters.Add ("@CodeType", SqlDbType.VarChar, 20). Value = "Grade Code"
Try
. ExecuteNonQuery ()
Return True
Catch ex as Exception
Return False
End Try
End Function
2. Call stored procedure, return normal value
Copy Code code as follows:
Private Function SqlProc1 (ByVal procname As String) as String
' Define data Link part omitted, myconn as linked object
Dim mycommand as New sqlclient.sqlcommand (procname, myconn)
With MyCommand
. CommandType = CommandType.StoredProcedure
. Parameters.Add ("@CodeType", SqlDbType.VarChar, 20). Value = "Grade Code"
. Parameters.Add ("@NewCode", SqlDbType.VarChar, 20). Direction = ParameterDirection.Output
Try
. ExecuteNonQuery ()
return. Parameters (1). Value ()
Catch ex as Exception
Return "No Code generation"
End Try
End Function
3. Calling a stored procedure, returning a dataset
' VB. NET code
Copy Code code as follows:
Private Function SqlProc2 (ByVal procname As String, ByVal Param1 as String) as DataSet
' Define the Command object and use the stored procedure
Dim mycommand as New Sqlclient.sqlcommand
myCommand.CommandType = CommandType.StoredProcedure
myCommand.CommandText = procname
Mycommand.connection = myconn
' Define a data adapter and set the parameters
Dim Mydapter as New sqlclient.sqldataadapter (mycommand)
MYDAPTER.SELECTCOMMAND.PARAMETERS.ADD ("@name", SqlDbType.VarChar, 20). Value = Param1
' Define a DataSet object and populate the dataset
Dim myDataSet as New DataSet
Try
Mydapter.fill (myDataSet)
Catch ex as Exception
End Try
Return myDataSet
End Function
' Stored procedure Code
Create Proc Test @name varchar AS
Select * from Ec_grade where cgradename= @name
Go
If you modify a stored procedure to some content, you can use it as a query
CREATE Proc Test
@name varchar (200) = '
--Here you should note that 200 is the length of the query conditions, depending on the actual situation, but not recommended for too long query conditions
As
Declare @sql1 varchar (8000)
If @name <> '
Select @sql1 = ' SELECT * from Ec_grade where ' + @name
Else
Select @sql1 = ' select * from Ec_grade '
EXEC (@sql1)
Go
