经常需要查一些信息， 想写视图来返回数据以提高效率，但是用试视图不能传参，只好想到改存储过程。记录一下语法，方便以后做项目时候想不起来了用。
1：传字段返回datatable
2： 传字段回一串字符
3: 传字符串返回datable
4：存储过程调用存储过程
--加半个小时
(select dateadd(MINUTE,30,GETDATE() ))--UnLockTime 往后加半个小时 CONVERT(varchar(100),@UnLockTime,20)
--转成可以拼接字符串的格式
set @strOutput=‘0~由于您最近输错5次密码已被锁定，请在‘+CONVERT(varchar(100),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 ON10 GO11 12 13 -- =============================================14 -- Author: <Author,name>15 -- Create date: <Create Date,>16 -- Description: 添加工作组人员时查找满足条件的审批人信息17 -- =============================================18 ALTER PROCEDURE [dbo].[proc_getIsAPProveRoleUserIDSelect]19 @ProjectID int,--项目ID20 @DepID int,--部门ID21 @RoleID1 int,--权限ID 22 @RoleID2 int,--权限ID23 @RoleID3 int--权限ID 24 25 AS26 BEGIN 27 select ID from t_user where [email protected] and State=0 and ([email protected] or [email protected] or RoleID=@RoleID3) 28 union29 select ID from t_user where ID in (30 select UserID as ID from t_User_Project where [email protected] and State=0) 31 and ([email protected] or [email protected] or RoleID=@RoleID3) 32 33 34 END35 GO36 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 /// 带参数执行存储过程并返回Datatable76 /// </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 [email protected] or AdAccount=@Account) 67 -- 1:查询用户名是否存在 68 if @ID>0--说明账号存在 69 begin 70 set @count=(select count(ID) from t_user where ([email protected] and [email protected]) or ([email protected] 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),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 @strOutput103 end104 end105 106 end 107 else -- 账号和密码不匹配，但是属于我们系统用户 。108 begin109 -- 根据ID给该用户加一次锁定次数，判断有没有到5次，有：更改锁定状态和解锁时间110 update t_user set PasswordIncorrectNumber=PasswordIncorrectNumber+1111 from t_user where ID=@ID 112 set @PasswordIncorrectNumber=(select PasswordIncorrectNumber from t_user where ID=@ID)113 if @PasswordIncorrectNumber>4114 begin115 set @UnLockTime=(select dateadd(MINUTE,30,GETDATE() ))--UnLockTime 往后加半个小时 CONVERT(varchar(100),20)116 update t_user set State=1,UnLockTime=@UnLockTime117 from t_user where [email protected] -- 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,NextUpdatePwdTime121 FROM t_user WHERE t_user.ID=@ID122 123 124 125 set @UnLockTime= CONVERT(varchar(100),20) 126 set @strOutput=‘0~由于您最近输错5次密码已被锁定，请在‘+CONVERT(varchar(100),20) +‘之后再尝试登录~‘+CAST(@ID AS NVARCHAR(10))127 select @strOutput128 end129 else --130 begin 131 132 set @strOutput=‘0~用户名或密码错误‘+‘~‘+CAST(@ID AS NVARCHAR(10))133 select @strOutput134 end 135 end 136 end 137 else --不存在 返回： 2~不是我们用户，不用加登录日志。138 begin139 set @strOutput=‘2~不是我们用户，不用加登录日志‘+‘~0‘140 select @strOutput141 end 142 end143 144 IF @@error <> 0 --发生错误145 146 BEGIN147 148 RolLBACK TRANSACTION149 set @strOutput=‘-1~发生错误~0‘150 151 SELECT @strOutput152 153 END154 155 ELSE156 157 BEGIN158 159 COMMIT TRANSACTION160 161 --执行成功 RETURN 1 162 163 SELECT @strOutput164 END165 END166 GO167 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 else197 {198 return "";199 }200 }201 202 //sqlhelper203 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 [email protected]*@pageindex; 37 set @sql=‘SELECT * FROM 38 ( 39 SELECT 40 ROW_NUMBER() over( order by fr.OptTimestamp DESC) as Num,‘; 41 42 set @[email protected]+‘ 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 @[email protected]+ @Str_filter; 67 68 set @[email protected]+‘ ) Info where Num between @a and @b ‘ 69 70 EXEC sp_executesql @sql,N‘@a int,@b int‘,@[email protected],@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 /// 带参数执行存储过程并返回Datatable102 /// </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 }VIEw Code
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 [email protected]_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,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 }VIEw Code 总结
以上是内存溢出为你收集整理的sqlserver存储过程里传字段、传字符串，并返回DataTable、字符串,存储过程调用存储过程。全部内容，希望文章能够帮你解决sqlserver存储过程里传字段、传字符串，并返回DataTable、字符串,存储过程调用存储过程。所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）