SQLTeam.com | Weblogs | Forums

Evaluating NULL in dynamic SQL

Hi

I have a couple of dynamic SQL statements that creates a script with 10 unioned statements, that I then create as a view. This is for 10 measured different items. I used dynamic SQL to be able to create variables within the SQL that would increment the various columns from 1-10, i.e. DayValue1, DayValue2 ....etc. then union them.

I have another one that does a similar thing but they columns are slightly different to first script, so I have to make placeholder columns to be able to union the statements.

In the placeholder I just used NULL as Placeholder1 etc.

In my completed data set some of the NULLS are coloured white and some yellow, plus some of the column names in the view's data types are strange like this:

Column1 (String40(NVARCHAR(40)))

An example of one of my placeholders is:

CAST(''NULL'' AS NVARCHAR(20)) AS Location

Note: 2 single quotes each side, as it is within a dynamic statement.

I used CAST as originally the placeholder column ended up set as an INT datatype, whereas in the other query it was Varchar (20), doing this has changed it to NVARCHAR(20), which matches the other column's type.

Would I be better using:

NULLIF('', '') AS Column

hello

so are you creating views dynamically? If so why? Are you getting new data sources, meaning your underlying tables get generated dynamically through some other system and hence you are forced to create views dynamically.

Rather than manually create a huge script with so many unions, i just use dynamic sql with loop that builds the statement for me. There are multiple tables for day which has up to 10 stage times that we need in one table to do reporting, If i need to change anything in the script I only need to change one bit of the statement and re-run it to regenerate the script with the change, rather than have to do the same change multiple through the union script.

ah gotcha. so it is a some sort of tolling you developed to help you get things done quicker. have you looked at using coalesce which does a null check as well.
would help if you showed us a snippet of where you need to use that in.

Doctor I need your to help me cure this pain I have on my thigh but I cant show it to you. I will tell you the symptoms just tell me what I need to do. It has pus, some bleeding and its makes hissing sounds. :astonished:

This produces the word NULL in the column and does not actually NULL out the field, that's why they are different colors. One is a string and one is actually null.

exec ('select CAST(''NULL'' AS NVARCHAR(20)) AS Location')

I'm not sure what else you want to see tbh? It's just as I asked should I be using NULL or empty string in a placeholder column. Th e code is basically like this, as the sort of pseudo code shows below:

Begin 
DECLARE <Variables>

While @tableName < 8
    -- Set Table name, there are 7 tables
  -- Set partial column numbers there are 10 numbers 

Select @TableName = 'Table1' etc
    

     While @n< 11     

      Set @Variables = 1,2, 3....etc

    SELECT @SQL = N'Select TableChecks'+@n+', '''''' AS [Place Holder] '
                                      ffom @tableName
      END
     
     --Combine all the 1-10 UNION for first table, then loop and do the next table, with 
     --1-10 again being looped through

     Select @SQLComb = @SQL

     -- Increment the table loop here to go to next table

END

I'm not sure what the issue is, but it will try to figure out what the datatypes are. For example:

this will create table t with all Integer columns

drop table t

select * into t
from (select 1 as Col1, Cast(null as varchar(2)) as Col2, 
		Cast(Null as nvarchar(3)) as Col3
union all
	select 2, 3, 4) v

This will fail because it sees strings and integers and can't cast string as int
drop table t

select * into t
from (select 1 as Col1, Cast(null as varchar(2)) as Col2, 
		Cast(Null as nvarchar(3)) as Col3
union all
	select 3, 'X', 'Y'
union all
	select 2, 3, 4
	) v

this works because the data types are all strings

drop table t

select * into t
from (select 1 as Col1, Cast(null as varchar(2)) as Col2,
Cast(Null as nvarchar(3)) as Col3
union all
select 3, 'X', 'Y'
union all
select 2, '3', '4'
) v

Are you on SQL 2012 (or later)?

later

Script is below. Lines with !! require at least a one-time set up on your part to specify the specific tables you want to compare. I used dummy tables, prefixed with #test_, in tempdb just to give a runnable example result from the code. Those tables can be dropped, the other temp tables are always needed by the main code.

USE tempdb; --!! chg to db name your tables reside in

--Create some dummy data tables, just to show the logic of column matching.
--These tables are NOT required once you substitute your own table names below.
IF OBJECT_ID('tempdb.dbo.#test_table1') IS NOT NULL
    DROP TABLE #test_table1;
CREATE TABLE #test_table1 (
    col1 varchar(30) NULL,
    col2 datetime NOT NULL,
    col3 int NULL,
    col4 bit NOT NULL
    );
IF OBJECT_ID('tempdb.dbo.#test_table2') IS NOT NULL
    DROP TABLE #test_table2;
CREATE TABLE #test_table2 (
    col1 varchar(40) NOT NULL,
    col2 date NULL,
    col3 bigint NOT NULL,
    col5 uniqueidentifier NULL
    )
IF OBJECT_ID('tempdb.dbo.#test_table3') IS NOT NULL
    DROP TABLE #test_table3;
CREATE TABLE #test_table3 (
    col1 varchar(50) NOT NULL,
    col2 datetime2 NULL,
    col6 nchar(2) NULL,
    col7 decimal(9, 2) NOT NULL
    )

IF OBJECT_ID('tempdb.dbo.#tables') IS NOT NULL
    DROP TABLE #tables;
CREATE TABLE #tables (
    table_name sysname NOT NULL, /*may include schema name, but not db name*/
    is_controlling_table bit NOT NULL DEFAULT 0
    )
CREATE UNIQUE CLUSTERED INDEX tables__CL ON #tables ( table_name ) WITH ( FILLFACTOR = 99 );

SET NOCOUNT ON;
--!!Fill in your table name(s) below. This part you MUST do.
INSERT INTO #tables VALUES
    ('#test_table1', 0), --<<--!!
    ('#test_table2', 0), --<<--!!
    ('#test_table3', 0)  --<<--!!

DECLARE @debug smallint
DECLARE @ignore_computed_columns bit
SET @debug = 0 --!!
SET @ignore_computed_columns = 1 --!!

--******************************************************************************

DECLARE @is_controlling_table bit
DECLARE @sql nvarchar(max)
DECLARE @table_name varchar(100)

--******************************************************************************

IF OBJECT_ID('tempdb.dbo.#columns') IS NOT NULL
    DROP TABLE #columns;
CREATE TABLE #columns (
    id int IDENTITY(1, 1) NOT NULL,
    table_name sysname NOT NULL,
    column_name varchar(100) NOT NULL
    )
CREATE UNIQUE CLUSTERED INDEX columns__CL ON #columns ( column_name, table_name ) WITH ( FILLFACTOR = 99 );
IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
    DROP TABLE #sql;
CREATE TABLE #sql (
    id int IDENTITY(1, 1) NOT NULL,
    sql_line varchar(8000) NULL
    )
CREATE UNIQUE CLUSTERED INDEX sql__CL ON #sql ( id ) WITH ( FILLFACTOR = 99 );

--******************************************************************************

INSERT INTO #columns ( table_name, column_name )
SELECT t.table_name, c.name AS column_name
FROM #tables t
INNER JOIN sys.columns c ON c.object_id = OBJECT_ID(t.table_name) AND
    (@ignore_computed_columns = 0 OR c.is_computed = 0)
--SELECT * FROM #columns

--******************************************************************************

DECLARE cursor_tables CURSOR LOCAL FAST_FORWARD FOR
SELECT table_name, is_controlling_table
FROM #tables
ORDER BY is_controlling_table DESC, table_name

OPEN cursor_tables

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM cursor_tables INTO @table_name, @is_controlling_table
    IF @@FETCH_STATUS <> 0
        BREAK;
    ;WITH cte_distinct_columns AS (
        SELECT DISTINCT column_name
        FROM #columns
    )
    SELECT @sql = STUFF(CAST(( --SELECT * FROM #columns
        SELECT N',' + CASE WHEN EXISTS(SELECT 1 FROM #columns c 
            WHERE c.column_name = cdc.column_name AND c.table_name = @table_name)
            THEN '[' + cdc.column_name + ']' ELSE 'NULL' END + ' AS [' + cdc.column_name + '] '
        FROM cte_distinct_columns cdc
        ORDER BY cdc.column_name
        FOR XML PATH('')
        ) AS nvarchar(max)), 1, 1, '')
    SET @sql = 'SELECT ' + @sql + 'FROM [' + @table_name
    INSERT INTO #sql
    SELECT @sql
    INSERT INTO #sql VALUES('UNION ALL')
END /*IF*/

DEALLOCATE cursor_tables
SET NOCOUNT OFF;

SELECT sql_line AS [--sql]
FROM #sql
WHERE id < (SELECT MAX(id) FROM #sql)
ORDER BY id

--******************************************************************************