sqlserver存储过程里传字段、传字符串，并返回DataTable、字符串,存储过程调用存储过程。
程序员文章站
2022-04-15 16:10:53
经常需要查一些信息， 想写视图来返回数据以提高效率，但是用试视图不能传参，只好想到改存储过程。记录一下语法，方便以后做项目时候想不起来了用。 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 }
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 }
上一篇： MySQL优化之执行计划