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

 

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

 

 

 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,