SQL Server 存储过程并在 VB.NET 中执行 [英] SQL Server stored procedure and execute in VB.NET

问题描述

这是一个有点旧的内容要讨论，但我需要有人可以解释我如何在 SQL Server 中创建存储过程以从过程中返回值，例如:

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.

我需要把它做成一个存储过程，并告诉我如何在 VB.NET 上执行它.也许我们假设名称会被提示为 LABEL1.TEXT，地址会被提示为 LABEL2.TEXT.

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.

我已经使用 return 改进了这个 SQL-Server 存储过程，但是在我执行它之后我没有任何返回

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

这是我的 VB.NET

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

推荐答案

假设你在 sqlserver 中有这个 sproc

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

请注意，当您期望 sproc 返回零个或一个记录时，这是标准方法.如果您有多个记录，那么您可以在 SqlDataReader.Read 方法上使用 while 循环，并且您应该提供存储返回记录的控件.

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.

这篇关于SQL Server 存储过程并在 VB.NET 中执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！