1

I'm trying to make a query that is somewhat universal to be run against differing versions of the same DB. In older versions, certain columns do not exist in the specific table I'm trying to address. The idea is for this to be run by someone who won't know what version they have. So, I'm trying to make the query check if a certain column exists (and if that one doesn't, none of the other questionable columns do either), then run one of two variations of the same script based on which columns exist and don't.

Masking some proprietary info, this is the problem area:

IF COL_LENGTH('dbo.users', '<column A>') IS NOT NULL -- Check for existence of <column A> column
                                                                   -- Some earlier versions do not have this column

                UPDATE Users
                SET Password = 'XXXX', <column A> = 0, <column B> = NULL
                WHERE Login = 'User' -- Reset password for later versions

            ELSE

                UPDATE Users
                SET Password = 'XXXX'
                WHERE Login = 'User' -- Reset password for earlier versions

            PRINT 'The Password for Login "User" has been reset to XXXX.'
        END

The problem I'm finding is that the entire script (There is more above this area) is erroring out and doing nothing because column A does not exist in the older version DB. It doesn't seem to be ignoring the first update statement shown if that column is not present.

Testing on the newer version DB is fully successful, though that one has column A.

Any ideas on this are appreciated. Thank you.

Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
captchevrolet
  • 13
  • 2
  • It would help if you tagged your post with the RDBMS you're using. – Marc Sep 26 '20 at 00:31
  • I apologize, as I'm not really a DBA, just someone who manages a specific program that uses a DB. What is RDBMS? – captchevrolet Sep 26 '20 at 00:33
  • Are you using Oracle? MySQL? PostgreSQL? MySQL? Microsoft SQL Server? Etc.? Tagging your post accordingly will convey some of the tooling that's available to you. – Marc Sep 26 '20 at 00:35
  • MS SQL SQL Management Studio. My apologies. – captchevrolet Sep 26 '20 at 00:39
  • Hi I just added the tag for you. "SQL Management Studio" is actually just a client that connects to your database. You are using sql-server or MSSQL Server – Jorge Campos Sep 26 '20 at 00:41
  • I suspect this question has your answer: https://stackoverflow.com/questions/133031/how-to-check-if-a-column-exists-in-a-sql-server-table – Marc Sep 26 '20 at 00:42
  • Reading that page before got me to where I am above. Unfortunately, while it helps with determining if the column is there, it doesn't seem to be helping with making the script choose one statement or the other. – captchevrolet Sep 26 '20 at 00:51
  • 1
    Put the code referencing the potentially non existent column inside EXEC so it is only compiled if that branch is taken to be executed – Martin Smith Sep 26 '20 at 00:59
  • You need to look at the meta-data for the table to determine if any particular column exists. And you need to do this dynamically - so you probably need help. Your first stop is [sys.columns](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-columns-transact-sql?view=sql-server-ver15). Use that to know if a table contains a column (with the appropriate joins to know the table name) – SMor Sep 26 '20 at 02:30

1 Answers1

0

The issue is that the script needs to be compiled . . . and you are missing the columns in the compilation. I might suggest:

IF EXISTS (SELECT 1
           FROM INFORMATION_SCHEMA.COLUMNS c
           WHERE TABLE_SCHEMA = 'dbo' AND
                 TABLE_NAME = 'users' AND
                 COLUMN_NAME = '<column A>'
          ) -- Check for existence of <column A> column
BEGIN
-- Some earlier versions do not have this column
    EXEC('
UPDATE Users
    SET Password = ''XXXX'', <column A> = 0, <column B> = NULL
    WHERE Login = ''User''
'); -- Reset password for later versions
END;
ELSE
BEGIN
    UPDATE Users
        SET Password = 'XXXX'
        WHERE Login = 'User' -- Reset password for earlier versions

    PRINT 'The Password for Login "User" has been reset to XXXX.'
END;

Both versions have Password, so the ELSE should compile fine.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786