存储过程进阶（vb.net+SQL Server2008环境）

写过一篇存储过程入门的博客，那仅仅是入门，下面和大家一起深入学习存储过程（也许以后还会有更深入^_^）

以经典的注册为例子，篇幅有限只写了核心部分，其他略过。

 

• 无参数无返回值的存储过程

以默认的信息进行注册

其中存储过程代码如下

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[MyProcedure1]    Script Date: 04/04/2011 20:55:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[MyProcedure1]
as
begin
insert into Login values ('admin','admin')
end

程序代码如下

    Dim Str As String = "Data Source=******;Initial Catalog=Test;User ID=sa;Password=*****"
    Dim conn As SqlConnection = New SqlConnection(Str)
    Private Sub btnNoParNoOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNoParNoOut.Click
        Dim sql As String = "execute MyProcedure1"
        Dim cmd As SqlCommand = New SqlCommand(sql, conn)
        Try
            conn.Open()
            If cmd.ExecuteNonQuery >= 0 Then
                MsgBox("Success"，，"无参数无返回值")
            Else
                MsgBox("Fail")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        conn.Close()
    End Sub

以上是最简单的存储过程的使用，其中Sqlcommand只是传递了字符串，并且其类型也是默认的类型未作更改，简单的可以理解为像SQL Server中传递了一个可以执行的语句。

 

• 有参数有返回值的存储过程

用户自定义信息注册

其中存储过程代码如下

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[MyProcedure2]    Script Date: 04/04/2011 21:00:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[MyProcedure2]
@UserID varchar(12),
@Pwd varchar(12)
as
begin
insert into Login values (@UserID,@Pwd )
end

程序代码如下

Private Sub btnParNoOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParNoOut.Click
       Dim sql As String = "execute MyProcedure2 @User, @Pwd"
       Dim cmd As SqlCommand = New SqlCommand(sql, conn)
       Dim parameter As SqlParameter
       parameter = New SqlParameter("@User", SqlDbType.VarChar)
       parameter.Value = Trim(txtParameter.Text)
       cmd.Parameters.Add(parameter)

       parameter = New SqlParameter("@Pwd", SqlDbType.VarChar)
       parameter.Value = Trim(txtPwd.Text)
       cmd.Parameters.Add(parameter)
       Try
           conn.Open()
           If cmd.ExecuteNonQuery >= 0 Then
               MsgBox("Success", , "有参数无返回值")
           Else
               MsgBox("Fail")
           End If
       Catch ex As Exception
           MsgBox(ex.Message)
       End Try
       conn.Close()
   End Sub

上面的存储过程是比较复杂的，但是并不难。只是加入了两个参数@UserID和@Pwd，既然有参数应该传递进入我们想要的值，vb.net 为我们提供了相应的方法（cmd.Parameters.Add）。这样一来程序以我们想要的值替换了字符串中的参数，然后交给SQL Server进行执行。

 

• 有参数有返回值的存储过程

用户输入ID后进行密码查询

其中存储过程代码如下

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[MyProcedure3]    Script Date: 04/04/2011 21:05:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[MyProcedure3]
@UserID varchar(12),
@Pwd varchar(12) output
as
begin
set @Pwd =( select Pwd from Login where UserID =@UserID)
end

程序代码如下

Private Sub btnParOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParOut.Click
        Dim sql As String = "MyProcedure3"
        Dim cmd As SqlCommand = New SqlCommand(sql, conn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add(New SqlParameter("@UserID", SqlDbType.VarChar))
        cmd.Parameters("@UserID").Value = Trim(txtParameter.Text)

        cmd.Parameters.Add(New SqlParameter("@Pwd", SqlDbType.VarChar, 12))
        cmd.Parameters("@Pwd").Direction = ParameterDirection.Output

        Try
            conn.Open()
            cmd.ExecuteReader()
            MsgBox(cmd.Parameters("@Pwd").Value.ToString(), , "有参数有返回值")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        conn.Close()
End Sub

其中需要格外注意的地方

1、 返回值的字符长度必须写，否则会出错。

2、 存储过程中返回值可以直接set@**=……，也可以最后return@**。

上面的存储过程比之前的更有深度，但也不是不可理解。首先改变了SqlCommand的类型，使其只针对于存储过程（即直接执行，无需在字符串中写明参数）。由于字符串（存储过程名）中没有参数，所以相应的参数直接通过SqlCommand传入SQL Server中。返回值是通过相应的方法（cmd.Parameters(“Pwd”).Direction=ParamterDIrection.OutPut）从数据库中取得。得到返回值后转换成字符串就可以显示出来。