SQL Server stored procedure and execute in VB.NET

sql, sql-server, sql-server-2008, stored-procedures, vb.net

Solution

Supposing you have this sproc in sqlserver

CREATE PROCEDURE GetNameAddress(@custID nvarchar(10))
as
BEGIN
SELECT NAME,ADDRESS FROM CUSTOMER WHERE IDCUSTOMER = @custID;
END

you call it and get the result in the standard way

' GetConnection is a method that creates and return the '
' SqlConnection used here according to your connection string'
Using cn = GetConnection()
   cn.Open()

   ' Create the command with the sproc name and add the parameter required'
   Dim cmd As SqlCommand = new SqlCommand("GetNameAddress", cn)
   cmd.CommandType = CommandType.StoredProcedure
   cmd.Parameters.AddWithValue("@custID", "DS212")

   ' Ask the command to create an SqlDataReader on the result of the sproc'
   Using r = cmd.ExecuteReader()

       ' If the SqlDataReader.Read returns true then there is a customer with that ID'
       if r.Read() then

           ' Get the first and second field frm the reader'
           lblName.Text = r.GetString(0)
           lblAddress.Text = r.GetString(1)
       end if
   End Using
End using

Notice that this is the standard approach when you expect zero or one record returned by the sproc. If you have more than one record then you use a while loop over the SqlDataReader.Read method and you should provide the controls where store the returned records.

Problem

This is a bit old-contents to be discussed but I need someone who can explain me how to create stored procedure in SQL Server for returning a value from procedure, example: ``` SELECT NAME, ADDRESS FROM CUSTOMER WHERE IDCUSTOMER = 'DS212'; ``` Then I need the name of its customer and the address instead. I need to make it as a stored procedure and show me how to execute it on VB.NET. Perhaps we assume that the name will be prompted to LABEL1.TEXT and the address will be prompted to LABEL2.TEXT. I've improved this SQL-Server Stored Procedure Using return but I have nothing to return after I execute it ``` CREATE PROCEDURE inserting_customer @custId varchar(10), @usr_id int AS BEGIN SET @usr_id = (SELECT MAX(SUBSTRING(CUSTOMER.idCustomer,3, LEN(CUSTOMER.IDCUSTOMER))) FROM CUSTOMER WHERE SUBSTRING(CUSTOMER.idCustomer,1,2) = @custId) END RETURN @usr_id GO ``` This is my VB.NET ``` conn.Open() Dim cmd As New SqlCommand("inserting_customer", conn) Try cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@custId", SqlDbType.VarChar) cmd.Parameters("@custId").Value = "YW" cmd.Parameters.Add("@usr_id", SqlDbType.Int) cmd.Parameters("@usr_id").Value = 0 cmd.ExecuteNonQuery() Finally If cmd IsNot Nothing Then cmd.Dispose() If conn IsNot Nothing AndAlso conn.State <> ConnectionState.Closed Then conn.Close() End Try ```

Original source