Yes, it is possible to create a stored procedure that takes parameters from a VB app.
Your stored procedure needs to be defined to take the parameters that you need. Also, your code to execute the stored procedure needs to set the parameters.
If you are using SQL Server, the correct syntax to create a stored procedure can be found
here. I am not well versed in creating stored procedures though, so that part of this question will be better resolved in the SQL forum.
As far as passing a parameter to a stored procedure in VB, the code looks something like this:
-
Dim payPeriodStartDate As Date = Date.Now.Date
-
Dim payPeriodEndDate As Date = payPeriodStartDate.AddDays(14)
-
-
Dim sqlConn As New SqlConnection("")
-
Dim sqlCmd As New SqlCommand("StoredProcedureName")
-
Dim param As SqlParameter
-
-
sqlCmd.Connection = sqlConn
-
sqlCmd.CommandType = CommandType.StoredProcedure
-
-
param = sqlCmd.CreateParameter()
-
param.ParameterName = "@payPeriodStartDate"
-
param.Value = payPeriodStartDate
-
param.SqlDbType = SqlDbType.Date
-
sqlCmd.Parameters.Add(param)
-
-
param = sqlCmd.CreateParameter()
-
param.ParameterName = "@payPeriodEndDate"
-
param.Value = payPeriodEndDate
-
param.SqlDbType = SqlDbType.Date
-
sqlCmd.Parameters.Add(param)
-
-
Dim dataReader As SqlDataReader = Nothing
-
-
Try
-
dataReader = sqlCmd.ExecuteReader()
-
'access your data
-
Catch ex As Exception
-
'whatever your exception handling is
-
End Try
-
Forgive me if this isn't 100% accurate. Its been a while sense I wrote this code in a production environment. I just quickly threw this together. The idea is correct though. Notice that the SqlCommand text is the stored procedure name and the SqlCommand object actually has a property that is set to explicitly state what type of command you are executing.