Dim command As New SqlCommand("sp_prueba", connection) 'Variable que contiene los comandos sql
command.CommandType = CommandType.StoredProcedure 'Indico que es un SP
With command.Parameters 'Paso los parámetros al SP
'El tipo es importante, si el tipo no está entre los que utiliza el SP, debo buscar uno que se le parezca
.Add(New SqlParameter("@param1", SqlDbType.Int)).Value = 3
'Todo SP retorna un valor, si se le pone el valor return al SP este regresará en la variable ReturnValue, o si no se lo pone, regresará el estado del SP (si se ejecutó o no)
.Add(New SqlParameter("ReturnValue", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
End With
'Lo que se ejecutó del SP se guarda en el adapter
Dim adapter As New SqlDataAdapter(command)
Generalmente utilizo el ReturnValue para validar que se ejecutó correctamente el SP, la mayoría no lo utiliza, puesto que utilizan otros métodos de validación. Al principio lo usaba, porque (siendo sincera) no sabia si en realidad se estaba ejecutando el SP y peor aún, si me traía la información que necesitaba de forma correcta sin todavía llenarlo en algún dataTable. Fue después de un tiempo que descubrí la instrucción command.ExecuteNonQuery, el cual sirve para obligar al SP a ejecutarse. Lo curioso es que no es obligatorio, con solo pasar los parámetros y añadirlo al adapter se ejecuta automáticamente. Una vez que tenemos los resultados en el adapter, podemos ponerlo en donde sea, un dataTable, un dataSet y demás yerbas, todo depende en donde lo queramos utilizar. Por ejemplo, para llenar el resultado en un DataTable, la instrucción sería así:
Dim dtTabla As New DataTable adapter.Fill(dtTabla) 'Aquí llenamos, con la instrucción Fill, los datos a la dataTable
La forma anterior, de pasar los parámetros, sirve para un SP que se ejecuta una sola vez, por ejemplo una consulta (select) normal. Pero si se desea que el SP se ejecute varias veces, por ejemplo para actualizar todas las filas de un grid, se utiliza de otra manera
Using connection As SqlConnection = New SqlConnection(args) 'La variable args contiene la cadena de conexión
connection.Open() 'Se debe abrir obligatoriamente la conexión cuando se trabaja con SPs de actualización o inserción
With command.Parameters 'Paso los parámetros al SP
.Add(New SqlParameter("@param1", SqlDbType.Int)) 'Esta vez solo añado el parámetro sin los datos
End With
Try
For Each row As DataGridViewRow In DataGridView1.Rows 'Recorro el grid que contiene mis datos
With command
.Parameters("@param1").Value = CInt(row.Cells(0).Value) 'Aquí le paso el valor al parámetro
End With
command.ExecuteNonQuery() 'Ejecuta el comando, en este caso el SP
Next
Catch expSQL As Exception
MsgBox(expSQL.ToString, MsgBoxStyle.OkOnly, "SQL Exception")
End Try
connection.Close() 'Si abro la conexión, aquí la cierro
End Using
El Try - Catch es para validar los errores que surgieran. Cuando se tienen variables de retorno, se utiliza de esta manera
With command.Parameters
'Primero se deben enviar los parámetros de entrada
.Add(New SqlParameter("@param1", SqlDbType.Int)).Value = valor
'Después se envían los parámetros de salida
.Add(New SqlParameter("@param_out1", SqlDbType.Int)).Direction = ParameterDirection.Output
End With
Try
'Aquí obtengo el valor del parámetro de salida
Dim var_out as Integer = CInt(command.Parameters("@param_out1").Value)
Catch expSQL As SqlException
MsgBox(expSQL.ToString, MsgBoxStyle.OkOnly, "SQL Exception")
End Try
Nota: Cuando el tipo de dato en el SP sea char o varchar, se tiene que indicar la longitud del parámetro, porque si no sale un bonito error. Para pasar la longitud, lo realizo así
.Add(New SqlParameter("@param_varchar", SqlDbType.VarChar, 200)).Direction = ParameterDirection.Output