Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# 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 = 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