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.
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.
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
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
--******************************************************************************
