Yes, and I would encourage it simply for the fact that it should get you out of that nasty habit of creating queries by piecing together commands and variables; which is the number one reason that people get SQL Injected
, which is still in the top 10 vulnerabilities list after being over 20 years old.
While this is neither a code translation nor generation website; what I am providing you below is simple boiler plate code and I would recommend you review the referenced articles at the end of this post.
I would also recommend that your Stored Procedure
should be written with a proper
and specifically identifying the fields in use. I would encourage you to format it over several lines for readability purposes.
SELECT a.RecordName, b.DetailName
FROM Records a
JOIN Details b ON a.RecordID = b.RecordID
WHERE a.RecordID = @RecordID
AND b.DetailName IS NOT NULL
Now onto creating an SP
You simply need to wrap that line of SQL into a
statement; and define the @Parameter
that will be used within it
CREATE PROCEDURE [SchemaName].[MyStoredProcedure] (
SELECT DISTINCT CHARGE, NO
WHERE CHARGE = ID
and NO = @No
and CHARGE is not null
And then modify your
code to reference this procedure, define this as a Stored Procedure, and add the
You will still run this via
and you can iterate through the records as normal.
Please Note: VB is not my native tongue so there may be errors as this was done in Notepad
Using connection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("SchemaName.MyStoredProcedure", connection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@No", myDataNvocc.Tables(0).Rows(y).Item("ID") )
Dim sdr as SqlDataReader = cmd.ExecuteReader()
Catch ex As Exception
MS Docs => CREATE PROCEDURE
MS Docs => SqlCommand.Parameters Property