Tag: SQL server

The easies way to do this using SQL Server 2017 and up;

DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT 
	@FileName ExcelFileName,
	--check there is a '.' in ExcelFileName
	CASE WHEN @FileName LIKE '%.%' THEN
		REVERSE(left(REVERSE(@FileName), CHARINDEX('.', REVERSE(@FileName)) - 1))
	ELSE ''
	END ExcelFileExtension
WHERE 1=1

Earlier versions can use this approach;

DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT 
	@FileName ExcelFileName,
	CASE 
         WHEN @FileName LIKE '%.%' THEN 
			RIGHT(@FileName, LEN(@FileName) - CHARINDEX('.', @FileName)) 
         ELSE '' 
       END ExcelFileExtension 
WHERE 1=1

Microsoft has introduced APPLY operator in SQL Server 2005. It allows joining between two table expressions, for example joining left/outer table expression with right/inner table expression. The way it works is that we have a table-valued expression on the right side and we want this table-valued expression to be evaluated for each row from the left table expression.

An ideal use case would be where we are unable to form any kind a join between two tables. For example a project table and calendar table.

(Left Side Table – Project Table)

(Right Side Table – Calendar Table)

Each ProjectEndDate falls between a FiscalQuarter. We need to append a new column in our result set that will be FiscalQuarterEndDate.

Here is the query;

SELECT p.ProjectKey, p.ProjectEndDate, dates.FiscalQuarterEndDate 
FROM Project p
CROSS APPLY
(
	SELECT FiscalQuarterEndDate 
	FROM FiscalCalendar calendar
	WHERE 1=1
	AND p.ProjectEndDate 
	BETWEEN calendar.FiscalQuarterStartDate AND calendar.FiscalQuarterEndDate
) dates
WHERE 1=1

Here is the result;

ProjectKey           ProjectEndDate                 FiscalQuarterEndDate

If we look at the result set, it returns only those rows that matches with the right table expression. Project Number XX-ABC13 is missing. CROSS APPLY is equivalent to an INNER JOIN. To be more precise its like a CROSS JOIN with a correlated sub-query).

If we want to return all rows from the left side then we need to apply OUTER APPLY which is equivalent to a LEFT OUTER JOIN.

Resources

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

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