{"id":386,"date":"2010-07-22T02:26:45","date_gmt":"2010-07-22T07:26:45","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=386"},"modified":"2010-07-22T02:32:21","modified_gmt":"2010-07-22T07:32:21","slug":"find-duplicated-variables-in-sql-server-script","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/find-duplicated-variables-in-sql-server-script\/","title":{"rendered":"Find duplicated variables in SQL Server script"},"content":{"rendered":"

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>\nDownload this demo: C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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. Here is a SQL Server script with variable ProductId declared twice. BEGIN TRAN DECLARE @ProductId BIGINT DECLARE @OTSProductId BIGINT SET @ProductId = 35768 SET @OTSProductId = @ProductId IF NOT EXISTS(SELECT ProductId FROM PRODUCT WHERE PRODUCTID = @ProductId) BEGIN INSERT INTO [Product] ([ProductId], [Name]) SELECT @ProductId, ‘Please see the insert in this ….’ END ELSE BEGIN PRINT REPLACE(‘product already exists’, ”, @ProductId) END DECLARE @ProductId BIGINT SET @ProductId = 35768 SET\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n