sqlserver存儲過程里傳欄位、傳字元串，並返回DataTable、字元串,存儲過程調用存儲過程。

經常需要查一些信息，想寫視圖來返回數據以提高效率，但是用試視圖不能傳參，只好想到改存儲過程。記錄一下語法，方便以後做項目時候想不起來了用。 1：傳欄位返回datatable 2：傳欄位回一串字元 3: 傳字元串返回datable 4：存儲過程調用存儲過程 --加半個小時(select datea ...

經常需要查一些信息，想寫視圖來返回數據以提高效率，但是用試視圖不能傳參，只好想到改存儲過程。記錄一下語法，方便以後做項目時候想不起來了用。

1：傳欄位返回datatable

2：傳欄位回一串字元

3: 傳字元串返回datable

4：存儲過程調用存儲過程

--加半個小時
(select dateadd(MINUTE,30,GETDATE() ))--UnLockTime 往後加半個小時 CONVERT(varchar(100), @UnLockTime, 20)

--轉成可以拼接字元串的格式
set @strOutput='0~由於您最近輸錯5次密碼已被鎖定，請在'+CONVERT(varchar(100), @UnLockTime, 20) +'之後再嘗試登錄~'+CAST(@Id AS NVARCHAR(10))

1：傳欄位返回datatable

 1 //傳欄位返回datatable 
 2 USE [ ]
 3 GO
 4 
 5 /****** Object:  StoredProcedure [dbo].[proc_getIsAPProveRoleUserIdSelect]    Script Date: 9/23/2019 10:35:46 AM ******/
 6 SET ANSI_NULLS ON
 7 GO
 8 
 9 SET QUOTED_IDENTIFIER ON
10 GO
11 
12 
13 -- =============================================
14 -- Author:        <Author,,Name>
15 -- Create date: <Create Date,,>
16 -- Description:     添加工作組人員時查找滿足條件的審批人信息
17 -- =============================================
18 ALTER PROCEDURE [dbo].[proc_getIsAPProveRoleUserIdSelect]
19      @ProjectId     int,  --項目id
20      @DepId     int , --部門id
21      @RoleId1     int , --許可權id 
22      @RoleId2     int ,   --許可權id
23      @RoleId3     int--許可權id 
24 
25 AS
26 BEGIN  
27     select id   from t_user  where   DepId=@DepId and    State=0  and  (RoleId=@RoleId1 or  RoleId=@RoleId2 or  RoleId=@RoleId3)  
28    union
29     select id   from t_user where  id  in (
30     select UserId  as id from  t_User_Project where ProjectId=@ProjectId  and    State=0) 
31      and   (RoleId=@RoleId1 or  RoleId=@RoleId2  or  RoleId=@RoleId3) 
32 
33       
34 END
35 GO
36 
37 
38   public static string getIsAPProveRoleUserId(int ProjectId, int DepId)
39         {
40             string Rtstr = ""; 
41             string strSql = string.Format("proc_getIsAPProveRoleUserIdSelect");
42             IList<KeyValue> sqlpara = new List<KeyValue>
43                                     {
44                                         new KeyValue{Key="@ProjectId",Value=ProjectId},
45                                         new KeyValue{Key="@DepId",Value=DepId},
46                                         new KeyValue{Key="@RoleId1",Value=Convert.ToInt32(UserRole.Administrators)}, 
47                                         new KeyValue{Key="@RoleId2",Value=Convert.ToInt32(UserRole.DepartmentLeader)}, 
48                                         new KeyValue{Key="@RoleId3",Value=Convert.ToInt32(UserRole.divisionManager) } 
49 
50                                     };
51             DataTable dt = sqlhelper.RunProcedureForDataSet(strSql, sqlpara);
52 
53 
54             if (dt != null && dt.Rows.Count > 0)
55             {
56                 for (int i = 0; i < dt.Rows.Count; i++)
57                 {
58                     Rtstr += dt.Rows[i]["id"].ToString() + ",";
59                 }
60             }
61             if (Rtstr.Length > 1)
62             {
63                 Rtstr = Rtstr.Remove(Rtstr.Length - 1, 1);
64             }
65             return Rtstr;
66         }
67 
68 
69 
70 
71 
72 
73 
74   /// <summary>
75         /// 帶參數執行存儲過程並返回DataTable
76         /// </summary>
77         /// <param name="str_conn">資料庫鏈接名稱</param>
78         /// <param name="str_sql">SQL腳本</param>
79         /// <param name="ilst_params">參數列表</param>
80         /// <returns></returns>
81         public  DataTable RunProcedureForDataSet(  string str_sql, IList<KeyValue> ilst_params)
82         {
83             using (SqlConnection sqlCon = new SqlConnection(connectionString))
84             {
85                 sqlCon.Open();
86                 DataSet ds = new DataSet();
87                 SqlDataAdapter objDa = new SqlDataAdapter(str_sql, sqlCon);
88                 objDa.SelectCommand.CommandType = CommandType.StoredProcedure;
89                 FillPram(objDa.SelectCommand.Parameters, ilst_params);
90                 objDa.Fill(ds);
91                 DataTable dt = ds.Tables[0];
92                 return dt;
93             }
94         }

View Code

2：傳欄位返回一串字元

  1 // 返回一串字元
  2 GO
  3 
  4 /****** Object:  StoredProcedure [dbo].[proc_LoginOutPut]    Script Date: 9/23/2019 1:04:29 PM ******/
  5 SET ANSI_NULLS ON
  6 GO
  7 
  8 SET QUOTED_IDENTIFIER ON
  9 GO
 10 
 11 
 12 -- =============================================
 13 -- Author:        <Author,,Name>
 14 -- Create date: <2019-04-25 15:00:00,>
 15 -- Description:    <登錄的方法>
 16 -- 查詢用戶名是否存在，
 17 --              不存在：
 18 --                返回： 用戶名或密碼錯誤 請檢查。
 19 --              存在：
 20 --                判斷用戶名和密碼是否匹配
 21 --                       匹配，看連續密碼輸入次數是否>0<5
 22 --                            是，清除次數， 直接登錄獲取更詳細信息———————— 返回
 23 --                            否：看解鎖時間是否大於等於當前時間（是：清除解鎖時間、清除次數、改狀態0），返回詳細信息
 24 --                                                                  （否：返回，您當前處於鎖定狀態，請在XX時間後進行登錄   ）
 25 --                       不匹配： 
 26 --                        根據account 查找id給該用戶加一次鎖定次數，判斷有沒有到5次，有：更改鎖定狀態和解鎖時間
 27 --                                                                               沒有：返回您輸入的賬號或密碼錯誤
 28 
 29 -- =============================================
 30   
 31 
 32 ALTER PROCEDURE [dbo].[proc_LoginOutPut]  
 33  @Account     varchar(20),  --賬號
 34  @Pwd    varchar(50),       --密碼
 35  @strOutput     VARCHAR(100) output   --輸出內容
 36   
 37    --輸出格式：0~由於您最近輸錯5次密碼已被鎖定，請在XX之後再嘗試登錄~id。  id 不存在寫0.存在寫自己id
 38            --0~用戶名或密碼錯誤~id。
 39            --    1~id~id
 40            --   -1~發生錯誤~id
 41  -- -1~發生錯誤 0不成功 1 登錄成功
 42 AS
 43 
 44 BEGIN 
 45   SET XACT_ABORT ON--如果出錯，會將transcation設置為uncommittable狀態
 46    declare @PasswordIncorrectNumber int --連續密碼輸入次數
 47    declare @Id int --用戶id
 48       declare @count int --用戶匹配行數
 49    declare @UnLockTime datetime --解鎖時間
 50   
 51     BEGIN TRANSACTION 
 52     -- 開始邏輯判斷
 53 
 54     ----------非空判斷
 55        if(@Account = '' or @Account is null  or @Pwd='' or @Pwd is null)
 56 
 57                 begin
 58                    set @strOutput='0~未獲取到信息，請稍後重試~0'
 59                   return @strOutput 
 60                 end
 61     ----------非空判斷結束
 62          
 63      
 64         else
 65                begin
 66               set  @Id=(select id  from   t_user   where  Account=@Account   or AdAccount=@Account)
 67                 -- 1:查詢用戶名是否存在
 68                  if   @Id>0--說明賬號存在
 69                       begin 
 70                       set  @count=(select count(id)  from   t_user   where  (Account=@Account and Pwd=@Pwd) or (AdAccount=@Account and Pwd=@Pwd))
 71                               if  @count=1
 72                                   begin 
 73                                         set @PasswordIncorrectNumber=(select  PasswordIncorrectNumber   from   t_user  where  id=@Id)
 74                                          --看連續密碼輸入次數是否>0 <5
 75                                           if   @PasswordIncorrectNumber<5
 76                                           begin
 77                                            --清除次數， 直接登錄獲取更詳細信息———————— 返回
 78                                            update t_user set  PasswordIncorrectNumber=0 ,UnLockTime=null ,State=0
 79                                                    from   t_user  where  id=@Id  
 80                                            set  @strOutput= '1~'+ '登錄成功'+'~'+CAST(@Id AS NVARCHAR(10))
 81                                      
 82                                                select  CAST(@strOutput AS NVARCHAR(20))
 83 
 84  
 85 
 86 
 87                                           end 
 88                                          else --次數大於5，已經被鎖住
 89                                               begin
 90                                               -- 看解鎖時間是否大於等於當前時間（是：清除解鎖時間、清除次數、改狀態0），返回詳細信息
 91                                                  set @UnLockTime=(select   [UnLockTime]   from   t_user  where  id=@Id)
 92                                                 if @UnLockTime>GETDATE()
 93                                                  begin
 94                                                    set @strOutput='0~由於您最近輸錯5次密碼已被鎖定，請在'+CONVERT(varchar(100), @UnLockTime, 20)  +'之後再嘗試登錄~'+CAST(@Id AS NVARCHAR(10))
 95                                                   -- select @strOutput
 96                                                   end
 97                                                  else --清除解鎖時間、清除次數、改狀態0
 98                                                     begin
 99                                                       update t_user set  PasswordIncorrectNumber=0 ,State=0,UnLockTime=null 
100                                                    from   t_user  where  id=@Id  
101                                                      set  @strOutput= '1~'+  '登錄成功'+'~'+CAST(@Id AS NVARCHAR(10))
102                                                     select @strOutput
103                                                     end
104                                               end
105                                            
106                                   end 
107                               else -- 賬號和密碼不匹配，但是屬於我們系統用戶  。
108                                   begin
109                                      -- 根據id給該用戶加一次鎖定次數，判斷有沒有到5次，有：更改鎖定狀態和解鎖時間
110                                       update t_user set  PasswordIncorrectNumber=PasswordIncorrectNumber+1
111                                                    from   t_user  where  id=@Id  
112                                        set @PasswordIncorrectNumber=(select  PasswordIncorrectNumber   from   t_user  where  id=@Id)
113                                             if   @PasswordIncorrectNumber>4
114                                              begin
115                                                  set @UnLockTime=(select dateadd(MINUTE,30,GETDATE() ))--UnLockTime 往後加半個小時 CONVERT(varchar(100), @UnLockTime, 20)
116                                                   update t_user set   State=1,UnLockTime=@UnLockTime
117                                                    from   t_user  where  id=@Id   -- State=1鎖定， 
118 
119                                                    INSERT INTO t_user_Log (pId , Account , AdAccount   , Pwd    , Name     , DepId    , RoleId    , Email  , Tel  , State    , PasswordIncorrectNumber    , UnLockTime      ,  CreateUserId  , NextUpdatePwdTime)
120                                                     SELECT  @Id,Account , AdAccount   , Pwd    , Name     , DepId    , RoleId    , Email  , Tel  , State    , PasswordIncorrectNumber    , UnLockTime      ,  CreateUserId  , NextUpdatePwdTime
121                                                      FROM t_user WHERE  t_user.Id=@Id
122                                                       
123 
124 
125                                                    set @UnLockTime=   CONVERT(varchar(100), @UnLockTime,  20) 
126                                                    set @strOutput='0~由於您最近輸錯5次密碼已被鎖定，請在'+CONVERT(varchar(100), @UnLockTime, 20) +'之後再嘗試登錄~'+CAST(@Id AS NVARCHAR(10))
127                                                    select @strOutput
128                                             end
129                                             else --
130                                                 begin 
131                                             
132                                                       set @strOutput='0~用戶名或密碼錯誤'+'~'+CAST(@Id AS NVARCHAR(10))
133                                                       select @strOutput
134                                                     end 
135                                   end 
136                       end 
137                  else --不存在 返回： 2~不是我們用戶，不用加登錄日誌。
138                       begin
139                        set @strOutput='2~不是我們用戶，不用加登錄日誌'+'~0'
140                        select @strOutput
141                       end 
142                end
143                 
144         IF @@error <> 0  --發生錯誤
145 
146         BEGIN
147 
148             ROLLBACK TRANSACTION
149             set @strOutput='-1~發生錯誤~0'
150              
151             SELECT @strOutput
152 
153         END
154 
155         ELSE
156 
157         BEGIN
158 
159             COMMIT TRANSACTION
160 
161          --執行成功   RETURN 1     
162       
163             SELECT  @strOutput
164          END
165   END
166 GO
167 
168 
169 //調用
170 
171   /// <summary>
172         /// 檢驗用戶賬號
173         /// </summary>
174         /// <param name="user"></param>
175         /// <returns></returns>
176         public static string CheckUser(EnUser user)
177         {
178 
179             string sql = string.Format("proc_LoginOutPut");
180 
181             List<KeyValue> paralist = new List<KeyValue>();
182             paralist.Add(new KeyValue { Key = "@Account", Value = user.Account });
183             paralist.Add(new KeyValue { Key = "@Pwd", Value = user.Pwd });
184             object Objreturn = SQLHelper.RunProcedureForObject(sql, "strOutput", paralist);
185             String returnStr = "";
186             if (Objreturn != null)
187             {
188                 returnStr = Objreturn.ToString();
189 
190             }
191             if (returnStr.Length > 0)
192             {
193                 return returnStr;
194 
195             }
196             else
197             {
198                 return "";
199             }
200         }
201 
202 //sqlhelper
203  
204               /// <summary>
205               /// 帶參數執行存儲過程並返回指定參數
206               /// </summary>
207               /// <param name="str_conn">資料庫鏈接名稱</param>
208               /// <param name="str_sql">SQL腳本</param>
209               /// <param name="str_returnName">返回值的變數名</param>
210               /// <param name="ilst_params">參數列表</param>
211               /// <returns>存儲過程返回的參數</returns>
212                public static object RunProcedureForObject( string str_sql, string str_returnName, IList<KeyValue> ilst_params)
213            {
214                using (SqlConnection sqlCon = new SqlConnection(connectionString))
215             {
216                   sqlCon.Open();
217                  SqlCommand sqlCmd = sqlCon.CreateCommand();
218                  sqlCmd.CommandType = CommandType.StoredProcedure;
219                  sqlCmd.CommandText = str_sql;
220                  FillPram(sqlCmd.Parameters, ilst_params);
221            //添加返回值參數
222                  SqlParameter param_outValue = new SqlParameter(str_returnName, SqlDbType.VarChar, 100);
223                 param_outValue.Direction = ParameterDirection.InputOutput;
224                   param_outValue.Value = string.Empty;
225                  sqlCmd.Parameters.Add(param_outValue);
226            //執行存儲過程
227                  sqlCmd.ExecuteNonQuery();
228                  //獲得存過過程執行後的返回值
229                   return param_outValue.Value;
230   }
231  }

View Code

3: 傳字元串返回datable

  1 //傳字元串返回datable
  2 //加整段查詢信息
  3 
  4 USE [FormSystem]
  5 GO
  6 
  7 /****** Object:  StoredProcedure [dbo].[proc_FormOperationRecordManagepage]    Script Date: 9/23/2019 1:06:14 PM ******/
  8 SET ANSI_NULLS ON
  9 GO
 10 
 11 SET QUOTED_IDENTIFIER ON
 12 GO
 13 
 14 
 15 
 16 
 17 
 18 
 19  
 20 -- =============================================
 21 -- Author:        <Author,,Name>
 22 -- Create date: <Create Date,,>
 23 -- Description:    
 24 -- =============================================
 25 ALTER  PROCEDURE [dbo].[proc_FormOperationRecordManagepage]
 26          @pagesize  int,       
 27          @pageindex  int,
 28          @Str_filter NVARCHAR(MAX) 
 29 AS 
 30 BEGIN 
 31 DECLARE  @sql NVARCHAR(MAX) ,
 32   @num1 int,
 33   @num2 int
 34 
 35   set @num1= @pagesize*(@pageindex-1)+1;
 36   set  @num2 =@pagesize*@pageindex;
 37 set @sql='SELECT * FROM
 38                 (
 39                      SELECT  
 40                             ROW_NUMBER() over(  order by fr.OptTimestamp  DESC) as Num,';
 41 
 42 set @sql=@sql+'    fr.[Id]
 43 ,tp.ProjectName
 44 ,td.DepName 
 45       ,tf.FormName
 46       ,ud.UploadFileName
 47       ,fr.OptName
 48       , tu1.Name as OptUserName 
 49       , tu2.Name as DownUserName 
 50       ,[Operationtime]
 51       ,[OptTimestamp] 
 52       ,fr.[Remark]
 53       ,ud.DownTime
 54       ,ud.Id as UploadDownloadId
 55     FROM [FormSystem].[dbo].[t_FormOperationRecord]  fr
 56     left  join t_UploadDownload ud   on   ud.id=fr.UploadDownloadId 
 57     left  join t_Form tf   on   tf.id=ud.FormId  
 58     left  join t_Project  tp    on tf.ProjectId=tp.Id
 59     left  join t_department  td    on tf.DepId=td.Id 
 60     left  join t_user  tu1    on tu1.Id=fr.OptUserId 
 61     left  join t_user  tu2    on tu2.Id=ud.DownUserId 
 62      where 1=1 '
 63     
 64          --加表單名稱查詢條件     tf.State=0
 65       if(@Str_filter != '' or @Str_filter !=null)
 66         set @sql=@sql+ @Str_filter;
 67            
 68   set @sql=@sql+'  ) Info where Num between  @a  and @b '          
 69  
 70      EXEC sp_executesql @sql ,N'@a int , @b int', @a=@num1,@b=@num2 
 71 END
 72 GO
 73 
 74 
 75 
 76  public static List<EnFormOperationRecord> GetFormOperationRecordList(int pageindex, int pagesize,
 77             object str_filter)
 78         {
 79             string strSql = string.Format("proc_FormOperationRecordManagepage");
 80             IList<KeyValue> sqlpara = new List<KeyValue>
 81                                     {
 82                                         new KeyValue{Key="@pagesize",Value=pagesize},
 83                                         new KeyValue{Key="@pageindex",Value=pageindex},
 84                                         new KeyValue{Key="@Str_filter",Value=str_filter}
 85                                     };
 86             DataTable dt = sqlhelper.RunProcedureForDataSet(strSql, sqlpara);
 87             List<EnFormOperationRecord> list = new List<EnFormOperationRecord>();
 88             if (dt != null && dt.Rows.Count > 0)
 89             {
 90                 for (int i = 0; i < dt.Rows.Count; i++)
 91                 {
 92                     EnFormOperationRecord tb = new EnFormOperationRecord();
 93                     tb.Num = Convert.ToInt16(dt.Rows[i]["Num"].ToString());
 94  }
 95             }
 96             return list;
 97         }
 98  
 99  
100  /// <summary>
101         ///