CODEWITHSUNDEEP
sql-serversqlcmd
Northernlad
Northernlad

Reputation: 177

Trying to parameterize the database in a sql test script

I'm trying to create a bunch of queries that can be used by my fellow testers that can be repeatable, and of course "parameter" driven.

One of my tests involves comparing the datatypes of one table against another, and I've tried to parameterize the query to the point that those using the query only need to amend the top area of the query, but I have the problem with one area, the database name.

I am not wanting to go down the Dynamic SQL route for this, but am looking for a way to get around the issue

    DECLARE @Source_DB      nvarchar(128) = 'SourceDB'
    ,       @Source_Table   nvarchar(128) = 'SourceTable'
    ,       @Source_Schema  nvarchar(128) = 'PJ';

    SELECT  *
    FROM    @Source_DB.INFORMATION_SCHEMA.COLUMNS isc  
    WHERE   isc.TABLE_NAME = @Source_Table AND isc.TABLE_SCHEMA = @Source_Schema

Full query below:

    DECLARE @Source_DB      nvarchar(128) = 'SourceDB'
    ,       @Source_Table   nvarchar(128) = 'SourceTable'
    ,       @Source_Schema  nvarchar(128) = 'SS'
    ,       @Target_DB      nvarchar(128) = 'TargetDB'
    ,       @Target_Table   nvarchar(128) = 'TargetTable'
    ,       @Target_Schema  nvarchar(128) = 'TS';

    SELECT  a.Source_TABLE_CATALOG
    ,       b.Target_TABLE_CATALOG  
    ,       a.Source_SCHEMA  
    ,       b.Target_SCHEMA  
    ,       a.Source_TABLE_NAME  
    ,       b.Target_TABLE_NAME  
    ,       a.Source_ORDINAL_POSITION  
    ,       b.Target_ORDINAL_POSITION  
    ,       a.Source_COLUMN_NAME  
    ,       b.Target_COLUMN_NAME  
    ,       a.Source_DATATYPE  
    ,       b.Target_DATATYPE  
    ,       a.Source_Nullable  
    ,       b.Target_Nullable  
    ,       a.Source_Default  
    ,       b.Target_Default
    FROM    (
            SELECT  isc.TABLE_CATALOG       AS [Source_TABLE_CATALOG]
            ,       isc.TABLE_SCHEMA        AS [Source_SCHEMA]  
            ,       isc.TABLE_NAME          AS [Source_TABLE_NAME]  
            ,       isc.ORDINAL_POSITION    AS [Source_ORDINAL_POSITION]  
            ,       isc.COLUMN_NAME         AS [Source_COLUMN_NAME]  
            ,       CASE 
                        WHEN isc.DATA_TYPE IN ('decimal', 'money', 'numeric') 
                            THEN CONCAT(isc.DATA_TYPE, '(', isc.NUMERIC_PRECISION, ',', isc.NUMERIC_SCALE, ')') 
                        WHEN isc.DATA_TYPE IN ('datetime','date','int') 
                            THEN isc.DATA_TYPE 
                        WHEN isc.DATETIME_PRECISION IS NOT NULL 
                            THEN CONCAT(isc.DATA_TYPE, '(', isc.DATETIME_PRECISION, ')') 
                        WHEN isc.DATA_TYPE = 'varchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 8000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                            THEN 'varchar(MAX)' 
                        WHEN isc.DATA_TYPE = 'nvarchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 4000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                            THEN 'nvarchar(MAX)' 
                        ELSE 
                            CONCAT(isc.DATA_TYPE, '(', isc.CHARACTER_MAXIMUM_LENGTH, ')') 
                    END AS [Source_DATATYPE]
            ,       CASE isc.IS_NULLABLE
                        WHEN 'YES' THEN 'NULL'
                        WHEN 'NO' THEN 'NOT NULL'
                    END AS [Source_Nullable]
            ,       isc.COLUMN_DEFAULT AS [Source_Default]
            FROM    @Source_DB.INFORMATION_SCHEMA.COLUMNS isc  
            WHERE   isc.TABLE_NAME = @Source_Table AND isc.TABLE_SCHEMA = @Source_Schema
            ) a
            JOIN    (
                    SELECT  isc.TABLE_CATALOG       AS [Target_TABLE_CATALOG]
                    ,       isc.TABLE_SCHEMA        AS [Target_SCHEMA]  
                    ,       isc.TABLE_NAME          AS [Target_TABLE_NAME]  
                    ,       isc.ORDINAL_POSITION    AS [Target_ORDINAL_POSITION]  
                    ,       isc.COLUMN_NAME         AS [Target_COLUMN_NAME]  
                    ,       CASE
                                WHEN isc.DATA_TYPE IN ('decimal', 'money', 'numeric')
                                    THEN CONCAT(isc.DATA_TYPE, '(', isc.NUMERIC_PRECISION, ',', isc.NUMERIC_SCALE, ')')
                                WHEN isc.DATA_TYPE IN ('datetime','date','int')
                                    THEN isc.DATA_TYPE
                                WHEN isc.DATETIME_PRECISION IS NOT NULL
                                    THEN CONCAT(isc.DATA_TYPE, '(', isc.DATETIME_PRECISION, ')')
                                WHEN isc.DATA_TYPE = 'varchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 8000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                                    THEN 'varchar(MAX)'
                                WHEN isc.DATA_TYPE = 'nvarchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 4000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                                    THEN 'nvarchar(MAX)'
                                ELSE
                                    CONCAT(isc.DATA_TYPE, '(', isc.CHARACTER_MAXIMUM_LENGTH, ')')
                            END AS [Target_DATATYPE]
                    ,       CASE isc.IS_NULLABLE
                                WHEN 'YES' THEN 'NULL'
                                WHEN 'NO' THEN 'NOT NULL'
                            END AS [Target_Nullable]
                    ,       isc.COLUMN_DEFAULT      AS [Target_Default]
                    FROM    @Target_DB.INFORMATION_SCHEMA.COLUMNS isc
                    WHERE   isc.TABLE_NAME = @Target_Table AND isc.TABLE_SCHEMA = @Target_Schema 
                    ) b ON  a.Source_COLUMN_NAME = b.Target_COLUMN_NAME
    WHERE   a.Source_DATATYPE <> b.Target_DATATYPE OR
            a.Source_Nullable <> b.Target_Nullable OR
            a.Source_Default <> b.Target_Default

Ideal outcome is that I can get the SQL query to work, but It may prove that this is something that cannot be done outside of a dynamic query, which wouldn't cause an issue from my point as the code would be held in a text file (with all the other templated queries), and would not run until the parameters were entered.

Upvotes: 0

Views: 323

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Identifiers in T-SQL statements cannot be parameterized. However, you can use SQLCMD scripting variables so that the variables are replaced before the T-SQL query is compiled and executed, functionally like a text search/replace.

The example below uses SQLCMD scripting variables instead of T-SQL variables/parameters. Scripts with SQLCMD variables can be run from SSMS in SQLCMD mode (Query-->Options-->SQLCMD Mode) or via the SQLCMD command-line utility. 

:SETVAR SOURCE_DB YourSourceDatabase
:SETVAR TARGET_DB YourTargetDatabase

DECLARE
            @Source_Table   nvarchar(128) = 'SourceTable'
    ,       @Source_Schema  nvarchar(128) = 'SS'
    ,       @Target_Table   nvarchar(128) = 'TargetTable'
    ,       @Target_Schema  nvarchar(128) = 'TS';

    SELECT  a.Source_TABLE_CATALOG
    ,       b.Target_TABLE_CATALOG  
    ,       a.Source_SCHEMA  
    ,       b.Target_SCHEMA  
    ,       a.Source_TABLE_NAME  
    ,       b.Target_TABLE_NAME  
    ,       a.Source_ORDINAL_POSITION  
    ,       b.Target_ORDINAL_POSITION  
    ,       a.Source_COLUMN_NAME  
    ,       b.Target_COLUMN_NAME  
    ,       a.Source_DATATYPE  
    ,       b.Target_DATATYPE  
    ,       a.Source_Nullable  
    ,       b.Target_Nullable  
    ,       a.Source_Default  
    ,       b.Target_Default
    FROM    (
            SELECT  isc.TABLE_CATALOG       AS [Source_TABLE_CATALOG]
            ,       isc.TABLE_SCHEMA        AS [Source_SCHEMA]  
            ,       isc.TABLE_NAME          AS [Source_TABLE_NAME]  
            ,       isc.ORDINAL_POSITION    AS [Source_ORDINAL_POSITION]  
            ,       isc.COLUMN_NAME         AS [Source_COLUMN_NAME]  
            ,       CASE 
                        WHEN isc.DATA_TYPE IN ('decimal', 'money', 'numeric') 
                            THEN CONCAT(isc.DATA_TYPE, '(', isc.NUMERIC_PRECISION, ',', isc.NUMERIC_SCALE, ')') 
                        WHEN isc.DATA_TYPE IN ('datetime','date','int') 
                            THEN isc.DATA_TYPE 
                        WHEN isc.DATETIME_PRECISION IS NOT NULL 
                            THEN CONCAT(isc.DATA_TYPE, '(', isc.DATETIME_PRECISION, ')') 
                        WHEN isc.DATA_TYPE = 'varchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 8000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                            THEN 'varchar(MAX)' 
                        WHEN isc.DATA_TYPE = 'nvarchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 4000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                            THEN 'nvarchar(MAX)' 
                        ELSE 
                            CONCAT(isc.DATA_TYPE, '(', isc.CHARACTER_MAXIMUM_LENGTH, ')') 
                    END AS [Source_DATATYPE]
            ,       CASE isc.IS_NULLABLE
                        WHEN 'YES' THEN 'NULL'
                        WHEN 'NO' THEN 'NOT NULL'
                    END AS [Source_Nullable]
            ,       isc.COLUMN_DEFAULT AS [Source_Default]
            FROM    [$(SOURCE_DB)].INFORMATION_SCHEMA.COLUMNS isc  
            WHERE   isc.TABLE_NAME = @Source_Table AND isc.TABLE_SCHEMA = @Source_Schema
            ) a
            JOIN    (
                    SELECT  isc.TABLE_CATALOG       AS [Target_TABLE_CATALOG]
                    ,       isc.TABLE_SCHEMA        AS [Target_SCHEMA]  
                    ,       isc.TABLE_NAME          AS [Target_TABLE_NAME]  
                    ,       isc.ORDINAL_POSITION    AS [Target_ORDINAL_POSITION]  
                    ,       isc.COLUMN_NAME         AS [Target_COLUMN_NAME]  
                    ,       CASE
                                WHEN isc.DATA_TYPE IN ('decimal', 'money', 'numeric')
                                    THEN CONCAT(isc.DATA_TYPE, '(', isc.NUMERIC_PRECISION, ',', isc.NUMERIC_SCALE, ')')
                                WHEN isc.DATA_TYPE IN ('datetime','date','int')
                                    THEN isc.DATA_TYPE
                                WHEN isc.DATETIME_PRECISION IS NOT NULL
                                    THEN CONCAT(isc.DATA_TYPE, '(', isc.DATETIME_PRECISION, ')')
                                WHEN isc.DATA_TYPE = 'varchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 8000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                                    THEN 'varchar(MAX)'
                                WHEN isc.DATA_TYPE = 'nvarchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 4000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                                    THEN 'nvarchar(MAX)'
                                ELSE
                                    CONCAT(isc.DATA_TYPE, '(', isc.CHARACTER_MAXIMUM_LENGTH, ')')
                            END AS [Target_DATATYPE]
                    ,       CASE isc.IS_NULLABLE
                                WHEN 'YES' THEN 'NULL'
                                WHEN 'NO' THEN 'NOT NULL'
                            END AS [Target_Nullable]
                    ,       isc.COLUMN_DEFAULT      AS [Target_Default]
                    FROM    [$(TARGET_DB)].INFORMATION_SCHEMA.COLUMNS isc
                    WHERE   isc.TABLE_NAME = @Target_Table AND isc.TABLE_SCHEMA = @Target_Schema 
                    ) b ON  a.Source_COLUMN_NAME = b.Target_COLUMN_NAME
    WHERE   a.Source_DATATYPE <> b.Target_DATATYPE OR
            a.Source_Nullable <> b.Target_Nullable OR
            a.Source_Default <> b.Target_Default;

With the SQLCMD utility, you can alternatively use -v arguments to pass the desired values on the command line instead of in the script itself.

Upvotes: 2

Related Questions