經常需要查一些資訊， 想寫視圖來回傳資料以提高效率，但是用試視圖不能傳參，只好想到改存盤程序，記錄一下語法，方便以后做專案時候想不起來了用，
1：傳欄位回傳datatable
2： 傳欄位回一串字符
3: 傳字串回傳datable
4：存盤程序呼叫存盤程序
5：存盤程序里寫分頁，單表存盤程序，多表存盤程序
6:sqlserver里檢驗寫好的存盤程序遇到的問題（分頁回傳table）段2 為解決辦法，
--加半個小時
(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=https://www.cnblogs.com/xiangrikui94/p/ProjectId}, 45 new KeyValue{Key="@DepId",Value=https://www.cnblogs.com/xiangrikui94/p/DepId}, 46 new KeyValue{Key="@RoleId1",Value=https://www.cnblogs.com/xiangrikui94/p/Convert.ToInt32(UserRole.Administrators)}, 47 new KeyValue{Key="@RoleId2",Value=https://www.cnblogs.com/xiangrikui94/p/Convert.ToInt32(UserRole.DepartmentLeader)}, 48 new KeyValue{Key="@RoleId3",Value=https://www.cnblogs.com/xiangrikui94/p/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,,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 =https://www.cnblogs.com/xiangrikui94/p/ user.Account }); 183 paralist.Add(new KeyValue { Key = "@Pwd", Value =https://www.cnblogs.com/xiangrikui94/p/ 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 = https://www.cnblogs.com/xiangrikui94/p/new SqlParameter(str_returnName, SqlDbType.VarChar, 100); 223 param_outValue.Direction = ParameterDirection.InputOutput; 224 param_outValue.Value = https://www.cnblogs.com/xiangrikui94/p/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 =@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=https://www.cnblogs.com/xiangrikui94/p/pagesize}, 83 new KeyValue{Key="@pageindex",Value=https://www.cnblogs.com/xiangrikui94/p/pageindex}, 84 new KeyValue{Key="@Str_filter",Value=https://www.cnblogs.com/xiangrikui94/p/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 }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 --@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 =https://www.cnblogs.com/xiangrikui94/p/ adress }); 62 paralist.Add(new KeyValue { Key = "@subTitle", Value =https://www.cnblogs.com/xiangrikui94/p/ Subject }); 63 paralist.Add(new KeyValue { Key = "@msg", Value =https://www.cnblogs.com/xiangrikui94/p/ content }); 64 paralist.Add(new KeyValue { Key = "@SendUserId", Value =https://www.cnblogs.com/xiangrikui94/p/ EmailLog.SendUserId }); 65 paralist.Add(new KeyValue { Key = "@ControlLevel", Value =https://www.cnblogs.com/xiangrikui94/p/ EmailLog.ControlLevel }); 66 paralist.Add(new KeyValue { Key = "@UploadDownloadId", Value =https://www.cnblogs.com/xiangrikui94/p/ EmailLog.UploadDownloadId }); 67 paralist.Add(new KeyValue { Key = "@ReceivedUserId", Value =https://www.cnblogs.com/xiangrikui94/p/ 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 = https://www.cnblogs.com/xiangrikui94/p/new SqlParameter(str_returnName, SqlDbType.VarChar, 100); 92 //param_outValue.Direction = ParameterDirection.InputOutput; 93 //param_outValue.Value = https://www.cnblogs.com/xiangrikui94/p/string.Empty; 94 //sqlCmd.Parameters.Add(param_outValue); 95 //執行存盤程序 96 return sqlCmd.ExecuteNonQuery(); 97 //獲得存過程序執行后的回傳值 98 //return param_outValue.Value; 99 } 100 }View Code
5：存盤程序里寫分頁
呼叫：
USE [eSystem]
GO
DECLARE @return_value int
EXEC @return_value = https://www.cnblogs.com/xiangrikui94/p/[dbo].[xsp_ination]
@tblName = N't_FormOperationRecord',
@strGetFields = N'Id,UploadDownloadId ,Operationtime,OptName',
@fldName = N'Id',
@PageSize = 10,
@PageIndex = 220,
@OrderType = 1
SELECT 'Return Value' = @return_value
GO
sql = "EXEC [dbo].[xsp_ination] \"tblNEWS\",\"*\",\"id\",40," + pindex.ToString() + ",1,\"iType=" + type.ToString();
SqlDataReader sr = ExecuteReader(sql);
while (sr.Read())
{
...
}
sr.Close();
View Code
6：檢驗寫好的存盤程序查不出結果集（分頁回傳table），段2 為解決辦法，
寫了一個分頁模板的存盤程序見下段代碼，在sqlserver工具，選定執行的存盤程序，右鍵execute，然后就輸入各個引數，執行完畢啥都沒有，語法如段1， 然后以為存盤程序寫的有問題，換了很多種方法，結果集都為空，然后搜到了一個別人的檢驗的語法，如段2，就出來了，仔細看了，段1 @return_value 回傳整個查詢結果，可是為什么 自定義的是int 型 的，去掉了直接賦參執行存盤程序還是不出資料， 段二EXEC 直接執行存盤程序就回傳整個結果了， 找到了以前寫的單獨的存盤程序回傳table 的如段3，看到里面結束時候也沒有select，但是為什么直接在工具里execute就能出結果， 一時沒有想明白是什么原因，要去寫其他代碼了，有時間了在回來整理這個問題，
存盤程序
1 USE [ ] 2 GO 3 4 /****** Object: StoredProcedure [dbo].[proc_PagingStoredProcedure] Script Date: 7/22/2020 10:03:17 AM ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 12 13 14 ALTER 15 PROCEDURE [dbo].[proc_PagingStoredProcedure] 16 @TableFields NVARCHAR(512), 17 @TableName NVARCHAR(512), 18 @SqlWhere NVARCHAR(512), 19 @OrderBy NVARCHAR(64), 20 @PageIndex INT, 21 @PageSize INT, 22 @TotalCount INT OUTPUT 23 AS 24 DECLARE @SQL1 NVARCHAR(2048) , @SQL2 NVARCHAR(2048) --@SQL1和@SQL2最好設定為比較長的字串，否則會因為SQL陳述句過長而導致執行失敗 25 SET @SQL1 = N'SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS NID, ' 26 + @TableFields + ' FROM ' + @TableName + 27 ' WHERE ' + @SqlWhere + ') as TmpTable WHERE TmpTable.NID BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex* @PageSize ' 28 SET @SQL2 = N'@TableFields NVARCHAR(512),@TableName NVARCHAR(512),@SqlWhere NVARCHAR(512),@OrderBy NVARCHAR(64),@PageIndex INT,@PageSize INT,@TotalCount INT OUTPUT' 29 EXEC SP_EXECUTESQL @SQL1, @SQL2, @TableFields, @TableName, @SqlWhere,@OrderBy,@PageIndex,@PageSize,@TotalCount OUTPUT 30 PRINT @SQL1 --列印執行陳述句 31 GO存盤程序
1 USE [XXG_PIP] 2 GO 3 4 DECLARE @return_value int, 5 @TotalCount int 6 7 EXEC @return_value =https://www.cnblogs.com/xiangrikui94/p/ [dbo].[proc_PagingStoredProcedure] 8 @TableFields = N'id', 9 @TableName = N't_State', 10 @SqlWhere = NULL, 11 @OrderBy = N'id', 12 @PageIndex = 1, 13 @PageSize = 3, 14 @TotalCount = @TotalCount OUTPUT 15 16 SELECT @TotalCount as N'@TotalCount' 17 18 19 GO段1
1 GO --測驗 2 DECLARE @Count INT = 0 3 EXEC proc_PagingStoredProcedure 4 'Id ,CreateTime ,CreateUserId 5 ,Remark 6 ,[State] 7 ,ISNULL( [LastUpdateUserId],0) as LastUpdateUserId 8 ,ISNULL( [StateName],''暫無'') as StateName 9 ,ISNULL( [LastUpdateTime],'''') as LastUpdateTime', --欄位 10 't_State', --表名 11 '1=1', --條件 12 'Id',--id 13 2, --頁碼 14 3,--行數 15 @Count OUTPUT段2
1 USE [FormSystem] 2 GO 3 4 /****** Object: StoredProcedure [dbo].[proc_getFormSelect] Script Date: 7/22/2020 10:49:56 AM ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 12 13 14 15 16 17 18 -- ============================================= 19 -- Author: <Author,,Name> 20 -- Create date: <Create Date,,> 21 -- Description: 22 -- ============================================= 23 CREATE PROCEDURE [dbo].[proc_getFormSelect] 24 @pagesize int, 25 @pageindex int, 26 @Str_filter NVARCHAR(MAX) 27 AS 28 29 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 tf.CreateTime DESC) as Num,'; 41 42 set @sql=@sql+' tf.Id , tf.FormName , tf.FilePath , tf.FormNumber , tf.DepId , td.DepName, 43 tf.ProjectId ,tp.ProjectName,tf.FileName, 44 tf.SetFlowId , tf.Remark , tf.state , tf.CreateUserId , tf.CreateTime ,tf.LastUpdateTime , tf.LastUpdateUserId, 45 tu2.name as CreateUser ,tu3.name as LastUpdateUser,tsf.FlowName from t_Form tf 46 left join t_department td on tf.DepId=td.id 47 left join t_Project tp on tf.ProjectId=tp.id 48 left join t_SetFlow tsf on tf.SetFlowId= tsf.id 49 left join t_user tu2 on tu2.Id= tf.CreateUserId 50 left join t_user tu3 on tu3.Id= tf.LastUpdateUserId 51 where 1=1 ' 52 53 --加表單名稱查詢條件 tf.State=0 54 if(@Str_filter != '' or @Str_filter !=null) 55 set @sql=@sql+ @Str_filter; 56 57 set @sql=@sql+' ) Info where Num between @a and @b ' 58 59 EXEC sp_executesql @sql ,N'@a int , @b int', @a=@num1,@b=@num2 60 END 61 GO段3
USE [XXG_PIP] GO /****** Object: StoredProcedure [dbo].[sp_PagingStoredProcedure2] Script Date: 7/28/2020 10:47:03 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[sp_PagingStoredProcedure2] @TableFields NVARCHAR(1000), @MainTable NVARCHAR(512), @ChildTable NVARCHAR(1000), @SqlWhere NVARCHAR(512), @OrderBy NVARCHAR(200), @PageIndex INT, @PageSize INT AS DECLARE @SQL1 NVARCHAR(2048) , @SQL2 NVARCHAR(2048) ,@DataNum NVARCHAR(20) --@SQL1和@SQL2最好設定為比較長的字串，否則會因為SQL陳述句過長而導致執行失敗 --查詢滿足條件資料有多少行 CREATE TABLE #TempTable (countNum INT ) INSERT INTO #TempTable EXEC [proc_Table_Count] @MainTable,@SqlWhere select @DataNum=countNum from #TempTable SET @SQL1 = N'SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS NID, ' + @TableFields +' ,'+@DataNum+' as countNum FROM ' + @MainTable + ' '+ @ChildTable+ ' WHERE ' + @SqlWhere + ') as TmpTable WHERE TmpTable.NID BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex* @PageSize ' SET @SQL2 = N'@TableFields NVARCHAR(1000),@MainTable NVARCHAR(512),@ChildTable NVARCHAR(1000),@SqlWhere NVARCHAR(512),@OrderBy NVARCHAR(100),@PageIndex INT,@PageSize INT' EXEC SP_EXECUTESQL @SQL1, @SQL2, @TableFields,@MainTable,@ChildTable, @SqlWhere,@OrderBy,@PageIndex,@PageSize PRINT @DataNum --列印執行陳述句 (select count (id) from t_State) , @DataNum PRINT @SQL1 --列印執行陳述句 GO USE [XXG_PIP] GO DECLARE @return_value int EXEC @return_value = [dbo].[sp_PagingStoredProcedure2] @TableFields = N'tl.Id, [LineName] , tl.CreateTime , tl.CreateUserId ', @MainTable = N't_Line tl', @ChildTable = N'left join t_State ts on ts.id= tl.StateID', @SqlWhere = N'1=1 ', @OrderBy = N'tl.id', @PageIndex = 1, @PageSize = 10 SELECT 'Return Value' = @return_value GO多表存盤程序分頁
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/39251.html
標籤：SQL Server
上一篇：SQL使用UPDATE和SUBSTRING截取字串方法,從頭截取到某個位置，截取中間片段，字串中間截取到末尾或洗掉前面的字串
下一篇：資料庫個人筆記（1）-- 基礎篇