Find duplicated variables in SQL Server script

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 @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

COMMIT TRAN 

This is the demo code in C# that can help you to find out duplciated varaibles in SQL script.

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;


using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;


namespace duplicateVar
{
    class duplicateVar
    {
        static void Main(string[] args)
        {
            string inputfile = "";
            string lcsqltext = "";

            if (args.Length == 0)
            {
                Console.WriteLine("duplicateVar scriptfile");
                return;
            }

            inputfile = args[0];

            //Reading from file
            try
            {
                using (StreamReader sr = new StreamReader(inputfile))
                {
                    //This allows you to do one Read operation.
                    lcsqltext = sr.ReadToEnd();
                }

            }
            catch (Exception e)
            {
                // Let the user know what went wrong.
                Console.WriteLine("File could not be read: " + e.Message);
                return;
            }


            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);
            sqlparser.SqlText.Text = lcsqltext;
            int i = sqlparser.Parse();

            if (i == 0) {
                StringBuilder sb = new StringBuilder(1024);
                foreach (TCustomSqlStatement stmt in sqlparser.SqlStatements)
                {
                    if (stmt.SqlStatementType == TSqlStatementType.sstMssqlDeclare)
                    {
                        TMssqlDeclare d = stmt as TMssqlDeclare;
                        if (d.declaretype == 1)
                        {
                            // declare variable
                            _TMssql_declare_var dv;
                            for (int k = 0; k < d.vars.Count(); k++)
                            {
                                dv = d.vars[k] as _TMssql_declare_var;
                                if (sb.ToString().IndexOf(dv._var_name.AsText) >= 0)
                                {
                                    // this variable already declared.
                                    Console.WriteLine("variable duplicated: "+dv._var_name.AsText+" pos("+dv._var_name.XPosition+","+dv._var_name.YPosition+")");
                                }
                                else
                                {
                                    sb.Append(dv._var_name.AsText);
                                    Console.WriteLine("variable declared: " + dv._var_name.AsText + " pos(" + dv._var_name.XPosition + "," + dv._var_name.YPosition + ")");                                    
                                }
                            }
                        }
                    }
                }
            }
            else
            {
                Console.WriteLine(sqlparser.ErrorMessages);
            }
        }
       
    }
}

This is the output when use this demo to check listed SQL script

variable declared: @ProductId pos(3,9)
variable declared: @OTSProductId pos(4,9)
variable duplicated: @ProductId pos(24,9)

Download this demo: C# version