sqlServer2008升级2016raiserror语法修改工具
DECLARE sku_cursors CURSOR LOCAL FAST_FORWARD FOR SELECT skuno, Count(sysserialno), categoryname FROM mfsysproduct c (NOLOCK) where c.sysserialno = '1A21VU800-600-G_06D_010W_X1' GROUP BY skuno, categoryname OPEN sku_cursors FETCH NEXT FROM sku_cursors INTO @skuno, @syscount, @categoryname WHILE @@FETCH_STATUS = 0 BEGIN SELECT @tempqty = Sum(requestqty - packqty) FROM sdshipsku (NOLOCK) WHERE shiporderno = @shiporderno AND skuno = @skuno FETCH NEXT FROM sku_cursors INTO @skuno, @syscount, @categoryname END CLOSE sku_cursors DEALLOCATE sku_cursors
private void btnUpdate_Click(object sender, EventArgs e) { List<string> listFileName = Directory.GetFiles(@AppConfig.FilePath).ToList(); StringBuilder text = new StringBuilder(); bool twoLine = false; //StringBuilder strBox = new StringBuilder(); listFileName.ForEach(f => { File.Delete(@"D:\temp2\" + Path.GetFileName(f)); StreamWriter sw = new StreamWriter(@"D:\temp2\" + Path.GetFileName(f), true, Encoding.UTF8); using (FileStream fs = new FileStream(f, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { using (StreamReader sr = new StreamReader(fs, Encoding.UTF8)) { Regex regex = new Regex(@"^[0-9]{1,7}$"); while (sr.Peek() > -1) { string line = sr.ReadLine(); if (twoLine) { text.Append(line.Replace('\'', '"').Split('"').ToList()[0] + "',16,1)"); sw.WriteLine(text); text.Clear(); twoLine = false; continue; } if ((line.IndexOf("raiserror", StringComparison.CurrentCultureIgnoreCase) != -1) && (!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--")) && (!line.Contains("@")) && (!line.Trim().EndsWith("\"")) && (!line.Trim().EndsWith("'")) && (!line.Trim().EndsWith(";")) && (!line.Trim().EndsWith(")")) && (!(line.Split('\'').ToList().Where(x => !string.IsNullOrEmpty(x.Trim())).Last().ToUpper().Contains("RETURN"))) && (!(line.Split('\'').ToList().Where(x => !string.IsNullOrEmpty(x.Trim())).Last().ToUpper().Contains("END")))) { //RAISERROR 99999 'The record has already been changed by another person,please reload // the program and edit it again.' List<string> listRaise = new List<string>(); listRaise = Regex.Replace(line, "raiserror", "^", RegexOptions.IgnoreCase).Split('^').ToList(); listRaise.ForEach(x => { if (!string.IsNullOrEmpty(x.Trim())) { List<string> listSub = new List<string>(); listSub = x.Trim().Split('\'').ToList(); if (x.Contains("\"")) //RAISERROR 99999 "This Record Already Exists In This Table." listSub = x.Trim().Split('"').ToList(); //Regex regex2 = new Regex(@"(\([^\)]*.*\))"); if (regex.IsMatch(listSub[0].Trim())) { text.Append(string.Format(" RAISERROR ('{0}", listSub[1].ToString())); } } }); twoLine = true; continue; } //create PROCEDURE[dbo].[GenerateDatacollectionByHour_sp_test] //(line.ToUpper().Contains("PROCEDURE") || line.ToUpper().Contains("PROC")) && if (!string.IsNullOrEmpty(line.Trim()) && (line.ToUpper().Replace("\t", " ").Split(' ').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList()[0].Equals("CREATE")) && (line.ToUpper().Replace("PROCEDURE", "PROC").Contains("PROC"))) { if ((!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--"))) { // CREAT PROCEDURE[DBO].[CREATEWOBYMANU] --Created by Yang for Repair Check Function at TJ 2015-12-18 Begin //CREATE PROCEDURE [dbo].[ where SERIAL_NUMBER='7CE552P4CM']存储名带空格 text.Append(string.Format("ALTER PROCEDURE [{0}[{1}", line.Split('[').ToList().ToList()[1], line.Split('[').ToList().ToList()[2])); sw.WriteLine(text); text.Clear(); continue; } } //以end或return结尾的 考虑变量里含有return关键字//RAISERROR 99999 @_strReturnMsg if ((line.IndexOf("raiserror", StringComparison.CurrentCultureIgnoreCase) != -1) && (!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--")) && (line.ToUpper().Trim().EndsWith("END") || regex.IsMatch( line.ToUpper().Trim().Split(' ').ToList().Last()))) { //RAISERROR 99996 @retmsg end //RAISERROR 99996 @retmsg return 101 if (line.Contains("@")) { text.Append(string.Format(" RAISERROR (@{0},16,1) {1}", line.Split('@')[1].Split(' ').ToList().Where(x=>!string.IsNullOrEmpty(x.Trim())).First(), line.Split('@')[1].Remove(0, line.Split('@')[1].Split(' ').ToList().Where(x => !string.IsNullOrEmpty(x.Trim())).First().Length))); sw.WriteLine(text); text.Clear(); continue; } else //RAISERROR 99999 'Transaction type can not be NULL.' RETURN 100 //99999 'Transaction type can not be NULL.' end { text.Append(string.Format(" RAISERROR ('{0}',16,1) {1}", line.Split('\'').ToList()[1], line.Split('\'').ToList().Last().Replace(";", ""))); sw.WriteLine(text); text.Clear(); continue; } } if ((line.IndexOf("raiserror", StringComparison.CurrentCultureIgnoreCase) != -1) && (!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--"))) { List<string> listRaise = new List<string>(); listRaise = Regex.Replace(line, "raiserror", "^", RegexOptions.IgnoreCase).Split('^').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList(); if ( listRaise.Count == 1) { string x = listRaise[0].Trim(); if (x.ToArray().ToList().Where(s => !string.IsNullOrEmpty(s.ToString().Trim())).ToList()[0].ToString().Equals("(")) { //RAISERROR(@strError,16,1) //raiserror(15009, -1, -1, @objname, @dbname) //raiserror(15197, -1, -1, @objname) //raiserror(15471, -1, -1) //RAISERROR(@strRror, 16, 1) //RAISERROR (@ErrorMessage, // @ErrorSeverity, // @ErrorState // ); text.Append(string.Format("RAISERROR{0}", x)); sw.WriteLine(text); text.Clear(); continue; } List<string> listSub = new List<string>(); listSub = x.Trim().Split('\'').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList(); if (x.ToArray().ToList().Where(s => !string.IsNullOrEmpty(s.ToString().Trim())).ToList().Where(s => s.Equals('"')).Count() == 2) { // 99999 "This Record Already Exists In This Table." // 99999 'ERROR: Must save An Fail Analy!!!"' listSub = x.Trim().Split('"').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList(); } if (regex.IsMatch(listSub[0].Trim())) { text.Append(string.Format(" RAISERROR ('{0}',16,1)", listSub[1].ToString())); sw.WriteLine(text); text.Clear(); continue; } if (listSub[0].Contains("@")) { // 99999 @strRror //RAISERROR 99999 @_strReturnMsg (变量含有return关键字) // raiserror 99999 @res_msg; text.Append(string.Format(" RAISERROR (@{0},16,1)", (listSub[0].Split('@').ToList())[1].ToString().Replace(";",""))); sw.WriteLine(text); text.Clear(); continue; } } else { sw.WriteLine(line); } } else { sw.WriteLine(line); } } } } sw.Close(); }); MessageBox.Show("修改成功"); }
DECLARE sku_cursors CURSOR LOCAL FAST_FORWARD FOR
SELECT skuno,
Count(sysserialno),
categoryname
FROM mfsysproduct c (NOLOCK)
where c.sysserialno = '1A21VU800-600-G_06D_010W_X1'
GROUP BY skuno,
categoryname
OPEN sku_cursors
FETCH NEXT FROM sku_cursors INTO @skuno, @syscount, @categoryname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @tempqty = Sum(requestqty - packqty)
FROM sdshipsku (NOLOCK)
WHERE shiporderno = @shiporderno
AND skuno = @skuno
FETCH NEXT FROM sku_cursors INTO @skuno, @syscount, @categoryname
END
CLOSE sku_cursors
DEALLOCATE sku_cursors
