SQL Server存储过程

在重构机房的时候，有很多地方用到了存储过程，在这里小小的总结一下。

       存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定的存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程通过将处理数据的程序从客户应用程序移动到服务器，从而可以降低网络流量，并提高性能和数据完整性。

        1、创建

        主要有两种方法：

       (1)利用SQL Server Management Studio创建存储过程

        ①单击数据库前的"+"号，然后单击"可编程性"，找到"存储过程"，右击"新建存储过程"，系统会自动生成一个模板，只需要修改相应的方法即可。

        ②利用T-SQL创建存储过程

       

[sql] view plain copy print ?

1. CREATE PROCEDURE  过程名 
2. @Param  参数类型 
3. ....... 
4. @Param  参数类型  output 
5. ...... 
6. AS 
7. BEGIN 
8. 命令行或命令块 
9. END 

CREATE PROCEDURE  过程名
@Param  参数类型
.......
@Param  参数类型  output
......
AS
BEGIN
命令行或命令块
END

        例如，创建添加用户的存储过程：

       

[sql] view plain copy print ?

1. CREATE procedure [dbo].[pro_AddUser] 
2. @user_id varchar(11), 
3. @user_name varchar(11), 
4. @user_level varchar(10), 
5. @user_pwd varchar(11), 
6. @user_register varchar(10) 
7. as 
8. insert T_User values (@user_id ,@user_name,@user_level ,@user_pwd ,@user_register ) 

CREATE procedure [dbo].[pro_AddUser]
@user_id varchar(11),
@user_name varchar(11),
@user_level varchar(10),
@user_pwd varchar(11),
@user_register varchar(10)
as
insert T_User values (@user_id ,@user_name,@user_level ,@user_pwd ,@user_register )

         在这里有需要注意的几点：

        *过程名：尽量以pro_或者proc_开头，不建议使用使用sp_为前缀，因为SQL Server系统的存储过程都是以sp_开头，这样在查找的时候会先查找系统自身的，降低查询速度。

        *BEGIN...END..之间的语句，主要是存储过程要执行的任务：

            查询语句：SELECT

            插入语句：INSERT

            更新语句：UPDATE

            删除语句：DELETE

            如果存储过程返回输出参数或0，则使用OUTPUT

        2、调用

        (1)利用T-SQL调用存储过程            

[sql] view plain copy print ?

1. EXECUTE 过程名 [参数值，······] 

EXECUTE 过程名 [参数值，······]

       

        (2)在客户端代码端

        D层代码：

       

[vb] view plain copy print ?

1. Public Function Add_User(enuser As Entity.E_User) As Boolean Implements IUser.Add_User 
2.     Dim pt As SqlParameter() = {New SqlParameter("@user_id", enuser.UserID), _ 
3.                                 New SqlParameter("@user_name", enuser.UserName), _ 
4.                                 New SqlParameter("@user_level", enuser.Level), _ 
5.                                 New SqlParameter("@user_pwd", enuser.UserPwd), _ 
6.                                 New SqlParameter("@user_register", Entity.E_PubShare.str_id) 
7.                             } 
8.     cmdText = "pro_AddUser" 
9.     Return helper.ExecuteDataTable(cmdText, CommandType.Text, pt) 
10. End Function 

    Public Function Add_User(enuser As Entity.E_User) As Boolean Implements IUser.Add_User
        Dim pt As SqlParameter() = {New SqlParameter("@user_id", enuser.UserID), _
                                    New SqlParameter("@user_name", enuser.UserName), _
                                    New SqlParameter("@user_level", enuser.Level), _
                                    New SqlParameter("@user_pwd", enuser.UserPwd), _
                                    New SqlParameter("@user_register", Entity.E_PubShare.str_id)
                                }
        cmdText = "pro_AddUser"
        Return helper.ExecuteDataTable(cmdText, CommandType.Text, pt)
    End Function

        SqlHelper类：

       

[vb] view plain copy print ?

1. Public Function ExecuteDataTable(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal pt As SqlParameter()) As Boolean 
2.  
3.     Using con As New SqlConnection(strCon) 
4.         Dim cmd As SqlCommand = con.CreateCommand()             
5.  
6.         cmd.CommandText = cmdText             
7.         cmd.CommandType = CommandType.StoredProcedure 
8.         cmd.Parameters.AddRange(pt)           
9.  
10.         Try 
11.             con.Open() 
12.             Return cmd.ExecuteNonQuery() 
13.             cmd.Parameters.Clear() 
14.             
15.         Catch ex As Exception 
16.             Return Nothing 
17.             Throw ex 
18.         End Try 
19.  
20.     End Using 
21. End Function 

    Public Function ExecuteDataTable(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal pt As SqlParameter()) As Boolean

        Using con As New SqlConnection(strCon)
            Dim cmd As SqlCommand = con.CreateCommand()            

            cmd.CommandText = cmdText            
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddRange(pt)          

            Try
                con.Open()
                Return cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
               
            Catch ex As Exception
                Return Nothing
                Throw ex
            End Try

        End Using
    End Function

        3、反思总结

        存储过程并不算难，但是我在学习的过程中还是出现了不少问题，归根到底是对SQL语言还不够熟练。不过经过几次卡壳总算是磕磕绊绊的了解的差不多了，这也是我们重构机房的意义所在，把以前学习到的知识真正运用到实践中来，是一个查漏补缺完善知识网的过程。遇到问题是在所难免的，不要嫌麻烦，而要把它当成提高自己的机会，每个细小的困难克服了，最终会实现一个大的飞跃。