converting sql command query to stored procedure in VB.net

First you need to use your Sql Server Management Studio or your Server Explorer windows in Visual Studio to create the stored procedure

CREATE PROCEDURE SelectClaims
(
     @fname nvarchar(max), 
     @lname nvarchar(max), 
     @mi nvarchar(max), 
     @vdms nvarchar(max)
)
as

select 
    a.lname, a.fname, a.mi, a.vdms_no, b.lname, b.fname, b.mi, b.CLAIM_NO, 
    c.ADDRESS1, c.ADDRESS2, c.ADDRESS3, c.sex, c.SCHOOL, c.C_YEAR, c.COURSE,
    c.telephone_no, c.mobile_no, c.email_add, c.EFFECTIVITY_DATE, c.SCC_NO, c.MLIST_NO, 
    c.CATEGORY, c.DATE_FILED, c.relation_to_veteran, c.ENTITLEMENT_AWARD,
    c.REMARKS, c.UPDATED_BY, c.D_UPDATED 
from carlos.claims_veteran a left join carlos.claims_waivee b on a.vdms_no = b.vdms_no 
                             left join tobee.EBD_SCHBILLS c On b.claim_no = c.CLAIM_NO 
where a.lname like @lname and a.fname like @fname and a.mi like @mi and a.VDMS_no like @vdms

As you can see, this sp receives 4 parameters, all of type nvarchar and I have set their size to the max possible value (you should fine tune this value to the effective size of your relative fields)

Then in VB.NET you call this SP with this code

If CheckForText1() = False Then
    MessageBox.Show("Cannot search with an empty value!")
    frmVetsearch.Dispose()
    Exit Sub
Else
    Using cmdconn2 = New SqlConnection(sqlstr)
    Using cmd = New SqlCommand("SelectClaims", cmdconn2)
        cmdconn2.Open()
        cmd.Parameters.AddWithValue("@lname", txtLname.Text & "%")
        cmd.Parameters.AddWithValue("@fname", "%" & txtFname.Text & "%"
        cmd.Parameters.AddWithValue("@mi", "%" & txtMi.Text & "%"
        cmd.Parameters.AddWithValue("@vdms", txtVDMSNo.Text & "%"
        cmd.CommandType = CommandType.StoredProcedure

        .... you are missing the code that read back your values
        Using reader = cmd.ExecuteReader()
             .......
        End Using

    End Using
    End Using
End If

The important things here are

• The CommandType is set to CommandType.StoredProcedure to allow proper handling of the CommandText set to the name of a stored procedure instead of a sql statement.
• Every value that need to be passed to the stored procedure is added to the Parameters collection of the SqlCommand using AddWithValue. However, if you really need to get the maximum performance possible you should build every parameter specifying its size and type
• Every disposable object (SqlConnection, SqlCommand, SqlDataReader) are enclosed in a using statement that will ensure proper closing and disposing also in case of exceptions

I wish however warn you in using this approach. In a select like this, using a stored procedure, don't give a clear advantage against a proper parameterized query. From my point of view, I have found that using and maintaining a large number of stored procedures could easily become a real maintenance nightmare.

Suppose, for example, that a new version of your program needs to retrieve a new field.
Now you need to update both your code and your stored procedure. And don't forget that in some environments you need to request the help of a dedicated person ( A DBA) just to make any changes to the a database in production.