当前位置: 萬仟网 > IT编程>数据库>MSSQL > sqlserver存储过程里传字段、传字符串,并返回DataTable、字符串,存储过程调用存储过程。

sqlserver存储过程里传字段、传字符串,并返回DataTable、字符串,存储过程调用存储过程。

2019年09月23日 16:51  | 萬仟网IT编程  | 我要评论

 

           经常需要查一些信息,  想写视图来返回数据以提高效率,但是用试视图不能传参,只好想到改存储过程。记录一下语法,方便以后做项目时候想不起来了用。

 

 

 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= '1634454@163.com',
 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         }

 

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com