This demo helps to discover multiple sql variables with the same name declared within a script. Discover name collisions before send it to database server can avoid breaks during execution.<\/p>\n
Here is a SQL Server script with variable ProductId declared twice.\n<\/p>\n
\r\nBEGIN TRAN\r\n\r\nDECLARE @ProductId BIGINT\r\nDECLARE @OTSProductId BIGINT\r\n\r\nSET @ProductId = 35768\r\nSET @OTSProductId = @ProductId\r\n\r\nIF NOT EXISTS(SELECT ProductId\r\n FROM PRODUCT\r\n WHERE PRODUCTID = @ProductId)\r\n BEGIN\r\n INSERT INTO [Product]\r\n ([ProductId],\r\n [Name])\r\n SELECT @ProductId,\r\n 'Please see the insert in this ....'\r\n END\r\nELSE\r\n BEGIN\r\n PRINT REPLACE('product already exists', '', @ProductId)\r\n END\r\n\r\nDECLARE @ProductId BIGINT\r\n\r\nSET @ProductId = 35768\r\nSET @OTSProductId = @ProductId\r\n\r\nIF NOT EXISTS(SELECT ProductId\r\n FROM PRODUCT\r\n WHERE PRODUCTID = @ProductId)\r\n BEGIN\r\n INSERT INTO [Product]\r\n ([ProductId],\r\n [Name])\r\n SELECT @ProductId,\r\n 'Please see the insert in this ....'\r\n END\r\nELSE\r\n BEGIN\r\n PRINT REPLACE('product already exists', '', @ProductId)\r\n END\r\n\r\nCOMMIT TRAN \r\n<\/pre>\nThis is the demo code in C# that can help you to find out duplciated varaibles in SQL script.\n<\/p>\n
\r\nusing System;\r\nusing System.Collections.Generic;\r\nusing System.Text;\r\nusing System.IO;\r\n\r\n\r\nusing gudusoft.gsqlparser;\r\nusing gudusoft.gsqlparser.Units;\r\n\r\n\r\nnamespace duplicateVar\r\n{\r\n class duplicateVar\r\n {\r\n static void Main(string[] args)\r\n {\r\n string inputfile = \"\";\r\n string lcsqltext = \"\";\r\n\r\n if (args.Length == 0)\r\n {\r\n Console.WriteLine(\"duplicateVar scriptfile\");\r\n return;\r\n }\r\n\r\n inputfile = args[0];\r\n\r\n \/\/Reading from file\r\n try\r\n {\r\n using (StreamReader sr = new StreamReader(inputfile))\r\n {\r\n \/\/This allows you to do one Read operation.\r\n lcsqltext = sr.ReadToEnd();\r\n }\r\n\r\n }\r\n catch (Exception e)\r\n {\r\n \/\/ Let the user know what went wrong.\r\n Console.WriteLine(\"File could not be read: \" + e.Message);\r\n return;\r\n }\r\n\r\n\r\n TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);\r\n sqlparser.SqlText.Text = lcsqltext;\r\n int i = sqlparser.Parse();\r\n\r\n if (i == 0) {\r\n StringBuilder sb = new StringBuilder(1024);\r\n foreach (TCustomSqlStatement stmt in sqlparser.SqlStatements)\r\n {\r\n if (stmt.SqlStatementType == TSqlStatementType.sstMssqlDeclare)\r\n {\r\n TMssqlDeclare d = stmt as TMssqlDeclare;\r\n if (d.declaretype == 1)\r\n {\r\n \/\/ declare variable\r\n _TMssql_declare_var dv;\r\n for (int k = 0; k < d.vars.Count(); k++)\r\n {\r\n dv = d.vars[k] as _TMssql_declare_var;\r\n if (sb.ToString().IndexOf(dv._var_name.AsText) >= 0)\r\n {\r\n \/\/ this variable already declared.\r\n Console.WriteLine(\"variable duplicated: \"+dv._var_name.AsText+\" pos(\"+dv._var_name.XPosition+\",\"+dv._var_name.YPosition+\")\");\r\n }\r\n else\r\n {\r\n sb.Append(dv._var_name.AsText);\r\n Console.WriteLine(\"variable declared: \" + dv._var_name.AsText + \" pos(\" + dv._var_name.XPosition + \",\" + dv._var_name.YPosition + \")\"); \r\n }\r\n }\r\n }\r\n }\r\n }\r\n }\r\n else\r\n {\r\n Console.WriteLine(sqlparser.ErrorMessages);\r\n }\r\n }\r\n \r\n }\r\n}\r\n<\/pre>\nThis is the output when use this demo to check listed SQL script<\/p>\n
\r\nvariable declared: @ProductId pos(3,9)\r\nvariable declared: @OTSProductId pos(4,9)\r\nvariable duplicated: @ProductId pos(24,9)\r\n<\/pre>\n