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
JOIN
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
CREATE PROCEDURE
statement; and define the
@Parameter that will be used within it
CREATE PROCEDURE [SchemaName].[MyStoredProcedure] (
@No INT
) AS
BEGIN
SELECT DISTINCT CHARGE, NO
FROM student
, teacher
WHERE CHARGE = ID
and NO = @No
and CHARGE is not null
END
GO
And then modify your
SqlCommand
code to reference this procedure, define this as a Stored Procedure, and add the
Parameter
into it.
You will still run this via
ExecuteReader()
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") )
Try
connection.Open()
Dim sdr as SqlDataReader = cmd.ExecuteReader()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
References:
MS Docs => CREATE PROCEDURE[
^]
MS Docs => SqlCommand.Parameters Property[
^]