经常需要查一些信息， 想写视图来返回数据以提高效率，但是用试视图不能传参，只好想到改存储过程。记录一下语法，方便以后做项目时候想不起来了用。
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 }
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 }
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 /// 带参数执行存储过程并返回DataTable 102 /// </summary> 103 /// <param name="str_conn">数据库链接名称</param> 104 /// <param name="str_sql">SQL脚本</param> 105 /// <param name="ilst_params">参数列表</param> 106 /// <returns></returns> 107 public DataTable RunProcedureForDataSet( string str_sql, IList<KeyValue> ilst_params) 108 { 109 using (SqlConnection sqlCon = new SqlConnection(connectionString)) 110 { 111 sqlCon.Open(); 112 DataSet ds = new DataSet(); 113 SqlDataAdapter objDa = new SqlDataAdapter(str_sql, sqlCon); 114 objDa.SelectCommand.CommandType = CommandType.StoredProcedure; 115 FillPram(objDa.SelectCommand.Parameters, ilst_params); 116 objDa.Fill(ds); 117 DataTable dt = ds.Tables[0]; 118 return dt; 119 } 120 }
4：存储过程调用存储过程
1 //存储过程调用存储过程 2 3 USE[FormSystem] 4 GO 5 6 /****** Object: StoredProcedure [dbo].[proc_SendEmail] Script Date: 9/23/2019 1:09:46 PM ******/ 7 SET ANSI_NULLS ON 8 GO 9 10 SET QUOTED_IDENTIFIER ON 11 GO 12 13 14 15 -- ============================================= 16 -- Author: <Author,,Name> 17 -- Create date: <Create Date,,> 18 -- Description: 19 -- ============================================= 20 ALTER PROCEDURE[dbo].[proc_SendEmail] 21 @MailToAddress varchar(50) , 22 @subTitle varchar(200), 23 @msg varchar(max) , 24 @SendUserId int , 25 @ControlLevel int , 26 @UploadDownloadId int, 27 @ReceivedUserId int 28 AS 29 30 31 BEGIN 32 print @MailToAddress; 33 print @subTitle; 34 print @msg; 35 36 if(len(@MailToAddress)>10) 37 begin 38 EXEC msdb.dbo.sp_send_dbmail @recipients = @MailToAddress, 39 @copy_recipients= '', 40 --@blind_copy_recipients= '[email protected]', 41 @body= @msg, 42 @body_format= 'html', 43 @subject = @subTitle, 44 @profile_name = 'e-Form'; 45 begin 46 insert into t_EmailLog(UploadDownloadId, 47 ReceivedUserId, SendResult, SendUserId, ControlLevel, 48 EmailContent, Email) 49 values(@UploadDownloadId, @ReceivedUserId, 0, @SendUserId, 50 @ControlLevel, @msg, @MailToAddress); 51 end 52 end 53 END 54 GO 55 56 57 public static object Send(string Subject, string content, string adress, Ent_EmailLog EmailLog) 58 { 59 string sql = string.Format("proc_SendEmail"); 60 List<KeyValue> paralist = new List<KeyValue>(); 61 paralist.Add(new KeyValue { Key = "@MailToAddress", Value = adress }); 62 paralist.Add(new KeyValue { Key = "@subTitle", Value = Subject }); 63 paralist.Add(new KeyValue { Key = "@msg", Value = content }); 64 paralist.Add(new KeyValue { Key = "@SendUserId", Value = EmailLog.SendUserId }); 65 paralist.Add(new KeyValue { Key = "@ControlLevel", Value = EmailLog.ControlLevel }); 66 paralist.Add(new KeyValue { Key = "@UploadDownloadId", Value = EmailLog.UploadDownloadId }); 67 paralist.Add(new KeyValue { Key = "@ReceivedUserId", Value = EmailLog.ReceivedUserId }); 68 object Objreturn = SQLHelper.ProcedureForObject(sql, paralist); 69 return Objreturn; 70 } 71 72 73 /// <summary> 74 /// 带参数执行存储过程 75 /// </summary> 76 /// <param name="str_conn">数据库链接名称</param> 77 /// <param name="str_sql">SQL脚本</param> 78 /// <param name="ilst_params">参数列表</param> 79 public static object ProcedureForObject(string str_sql, IList<KeyValue> ilst_params) 80 { 81 //如果换到正式要把这里改成 82 using (SqlConnection sqlCon = new SqlConnection(connectionString2)) 83 // using (SqlConnection sqlCon = new SqlConnection(connectionString)) 84 { 85 sqlCon.Open(); 86 SqlCommand sqlCmd = sqlCon.CreateCommand(); 87 sqlCmd.CommandType = CommandType.StoredProcedure; 88 sqlCmd.CommandText = str_sql; 89 FillPram(sqlCmd.Parameters, ilst_params); 90 ////添加返回值参数 91 //SqlParameter param_outValue = new SqlParameter(str_returnName, SqlDbType.VarChar, 100); 92 //param_outValue.Direction = ParameterDirection.InputOutput; 93 //param_outValue.Value = string.Empty; 94 //sqlCmd.Parameters.Add(param_outValue); 95 //执行存储过程 96 return sqlCmd.ExecuteNonQuery(); 97 //获得存过过程执行后的返回值 98 //return param_outValue.Value; 99 } 100 }