Category: Data Design

When we receive data feed from outside vendors, the date values are often like this;

Purchase Date
2020-07-12
NULL
'n/a'
''

The challenge is how to parse these dates and load them in SQL server table. Here is one work around;

DECLARE @purchaseDate nvarchar(10) = '9/30/2020 12:00:00 AM'
--DECLARE @purchaseDate nvarchar(10) = ''
--DECLARE @purchaseDate nvarchar(10) = 'n/a'
--DECLARE @purchaseDate nvarchar(10) = NULL

SELECT 
	CASE 
	WHEN ISDATE(ISNULL(@myDate, NULL)) = 1 THEN TRY_PARSE(@myDate AS date)
	END PurchaseDate

We are basically checking whether value is of date, if yes then we apply transformation logic.

There are multiple methods that can be used to import stored procedures results into tables.

OPENROWSET AND OPENQUERY methods require that stored procedures don’t have;

1. temp tables inside
2. Don’t return duplicate columns

There is no need for a linked server, but you would need to get the connection string right. You need to specify the full path to the stored procedure including the database name and the stored procedure owner.

METHOD–1 Using OPENROWSET

This is one time step to configure database server.

--one time step
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

You can run your stored procedures that will load data into tables;

IF OBJECT_ID('tempdb..#STG_OPENROWSET') IS NOT NULL DROP TABLE #STG_OPENROWSET
SELECT * INTO #STG_OPENROWSET FROM OPENROWSET('SQLNCLI', 
'Server=(local);Trusted_Connection=yes;',
'EXEC DatabaseName.[dbo].[StoredProcedureName] @param1 = 1, @param2 = 2020')
SELECT * FROM #STG_OPENROWSET
DROP TABLE #STG_OPENROWSET

METHOD–2 Using OPENQUERY.

This is one time step to configure database server.

--one time step
Select @@ServerName
EXEC sp_serveroption @@ServerName, 'DATA ACCESS', TRUE

You can run your stored procedures that will load data into tables;

IF OBJECT_ID('tempdb..#STG_OPENQUERY') IS NOT NULL DROP TABLE #STG_OPENQUERY
SELECT  * INTO    #STG_OPENQUERY
FROM    OPENQUERY(DatabaseServerName, 
'EXEC	Database.[dbo].[StoredProcedureName] @param1 = 1, @param2 = 2020');
SELECT * FROM #STG_OPENQUERY
DROP TABLE #STG_OPENQUERY

If stored procedures are using temp tables or returning duplicate columns, you will get these errors using Method-1 and Method-2;

invalid object #tablename.

Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET”

OPENROWSET are not allowed in Azure SQL. They are allowed in a VM that is connected to Azure.

METHOD–3  Manually create temp tables

There is no database configuration required.

You can run your stored procedures that will load data into tables;

--the proce is using temp tables so this is work around
IF OBJECT_ID('tempdb..#STG_TempTable') IS NOT NULL DROP TABLE #STG_TempTable
CREATE TABLE #STG_TempTable
(
	ID int,
	[name] nvarchar(255),
	shortName nvarchar(25),
)
INSERT #STG_TempTable 
EXEC [dbo].[StoredProcedureName] @param1 = 1, @param2 = 2020
SELECT * FROM #STG_TempTable

By using this method, your database administrator will be happy because you are not making any security related changes at database server level.

References

https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table

This is how to check NULL or empty input values for a date column. Currently NULL or empty values produces ‘1900-01-01’ value which is not acceptable.

DECLARE @InputDate DATE

--set input to spaces
SET @InputDate = ''
--don't want 1900-01-01 output, instead NULL value
SELECT ISNULL(NULLIF(@InputDate, ''), NULL) AS InputDateSpaces

--set input to null
SET @InputDate = NULL
--don't want 1900-01-01 output, instead NULL value
SELECT ISNULL(NULLIF(@InputDate, ''), NULL) AS InputDateNULL

--set input to date
SET @InputDate = '2021-06-25'
--don't want 1900-01-01 output, instead date value
SELECT ISNULL(NULLIF(@InputDate, ''), NULL) AS InputDateRealDate

These are the results;