Author: Shahzad Khan

I would like to do something like this;

SELECT * INTO #tmpADD EXEC mySPROC 'Params'

SQL Server doesn’t support this and I don’t want to use OPENQUERY like this.

Select @@ServerName
EXEC sp_serveroption @@ServerName, 'DATA ACCESS', TRUE

SELECT  *
INTO    #tmpADD
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.mySPROC 1')

Using OPENROWSET will get the job done, but it will incur some additional overhead for opening up local connections and marshalling data. It also may not be an option in all cases since it requires an ad hoc query permission which poses a security risk and therefore may not be desired. Also, the OPENROWSET approach will preclude the use of stored procedures returning more than one result set. Wrapping multiple inline table-value user-defined functions in a single stored procedure can achieve this.

The first work around is to define structure of return values of stored procedure and create temp table.

CREATE TABLE #tmpADD
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpADD
Exec mySPROC 'Params'

This will work but if we ever add additional columns to the mySPROC stored procedure, this will blow up.

The second work around is to use an inline table-valued user-defined function. This is essentially a stored procedure (will take parameters) that returns a table as a result set; and therefore will place nicely with an INTO statement.

If we still have a driving need for a stored procedure, we can wrap the inline table-valued user-defined function with a stored procedure. The stored procedure just passes parameters when it calls select * from the inline table-valued user-defined function.

So for instance, Here is an inline table-valued user-defined function to get a list of customers for a particular region:

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

We can then call this function to get the results as such:

SELECT * FROM CustomersbyRegion(1)

Or to do a SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

If we still need a stored procedure, then wrap the function as such:

CREATE PROCEDURE mySPROC
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

I think this is the most ‘hack-less’ method to obtain the desired results. It uses the existing features as they were intended to be used without additional complications. By nesting the inline table-valued user-defined function in the stored procedure, we have access to the functionality in two ways. Plus! We have only one point of maintenance for the actual SQL code.

If inline table value functions returns more than one schema then its is not possible to use IF statement in it. In this case we have to use table value function not inline table value function;

-- =============================================
-- Author:		Shahzad Khan
-- Create date: 2/1/2022
-- Description:	returns zoo data
-- SELECT * FROM  [dbo].[fn_getZooAnimal](2)
-- =============================================
CREATE FUNCTION fn_getZooAnimal 
(
	-- Add the parameters for the function here
	@Id int
)
RETURNS 
@Table_Var TABLE 
(
	-- Add the column definitions for the TABLE variable here
	Id int, 
	AnimalName NVARCHAR(50)
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	IF (@Id = 1)
	BEGIN
		INSERT @Table_Var
		SELECT @Id, 'Monkey'
	END
	ELSE
	BEGIN
		INSERT @Table_Var
		SELECT @Id, 'Deer'
	END
	RETURN 
END
GO

Resources

https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table?page=1&tab=votes#tab-top

I have 2 tables one containing historical data using type 2 SCD (Slowly changing dimensions) called DimBrand and another containing just the latest dimension data called LatestDimBrand. Using the merge function I will insert new records from LatestDimBrand into DimBrand, I will archive (apply an end date) to any DimBrand records which do not appear in the latest data, and finally enter a new record and archive the old record for any Brands which may have changed.

DimBrand (the target of our Inserts, Updates, Deletes) and DimLatestBrand (the source for Inserts,Updates,Deletes):

Here is complete merge statement;

INSERT #DimBrand    ([BrandCode],[BrandName],[StartDate])
SELECT                 [BrandCode],[BrandName],getdate()                                    
FROM                (
                    MERGE #DimBrand AS Target
                    USING    (
                            SELECT    [BrandCode],[BrandName],[StartDate],[EndDate]                       
                            FROM    #LatestDimBrand
                            ) AS Source
                    ON     (Target.[BrandCode] = Source.[BrandCode])    
                    -------------------------------                       
                    WHEN MATCHED AND Target.[BrandName] <> Source.[BrandName]
                        THEN
                        UPDATE SET Target.[EndDate] = getdate()
                    -------------------------------
                    WHEN NOT MATCHED BY TARGET 
                        THEN
                        INSERT ( 
                                [BrandCode]                        
                                ,[BrandName]                    
                                ,[StartDate]              
                                ,[EndDate]                                            
                        )
                        VALUES (      
                                Source.[BrandCode], 
                                Source.[BrandName], 
                                Source.[StartDate],
                                Source.[EndDate]   
                        )
                    -------------------------------
                    WHEN NOT MATCHED BY SOURCE 
                        THEN 
                        UPDATE SET Target.[EndDate] = getdate()
                    -------------------------------
                    OUTPUT $Action, Source.*
                    ) As i([Action],[BrandCode],[BrandName],[StartDate],[EndDate])
                    -------------------------------
WHERE                [Action] = 'UPDATE'
AND                    BrandCode IS NOT NULL

Resources

https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

https://www.mssqltips.com/sqlservertip/2883/using-the-sql-server-merge-statement-to-process-type-2-slowly-changing-dimensions/

I have this paragraph;

Speeding ticket is 210.99USD. Aggravated DUI could add up 1 year jail time.

This is a standard text where the pattern is like this;

Speeding ticket is [Amount]. Aggravated DUI could add up [Term] year jail time.

The ask is to extract Amount and Term from this text. The approach is to split the paragraph and use SQL IsNumeric functions to extract the values;

Here is a sample prototype;

DECLARE @ParagraphText NVARCHAR(MAX) = N'Speeding ticket is 210.99USD. Aggrevated DUI could add up 1 year jail time.'

--table variable
DECLARE @Test TABLE (ValueColumn VARCHAR(8000))
INSERT @Test
--I am using a custom function but you can use string_split() on SQL 2017 and upward
SELECT * FROM dbo.fnSplitString(@ParagraphText, ' ')

--using windows ranking function to get both values
SELECT ROW_NUMBER() OVER(ORDER BY ValueColumn) [ROW_NUMBER],*
FROM
(
    SELECT
    CONVERT(DECIMAL(20,8),
    CASE 
	WHEN IsNumeric(ValueColumn)=1 THEN 	CONVERT(FLOAT,ValueColumn)
	ELSE CONVERT(FLOAT,'0'+LEFT(ValueColumn,PATINDEX('%[^0-9.]%',ValueColumn)-1))
    END) AS ExtractedColumn
    ,ValueColumn
    FROM @Test
) x
WHERE x.ExtractedColumn > 0

One of the most obvious and useful set of window functions are ranking functions where rows from the data set are ranked accordingly. There are three ranking functions:

ROW_NUMBER()
RANK()
DENSE_RANK()

The difference is easy to remember. For the examples, let’s assume we have this stocks data set.

IF OBJECT_ID('tempdb..#stocks') IS NOT NULL DROP TABLE #stocks;
;With Stocks AS
(
    SELECT 'MSFT' Symbol UNION ALL
    SELECT 'MSFT' Symbol UNION ALL
    SELECT 'MSFT' Symbol UNION ALL
    SELECT 'AAPL' Symbol UNION ALL
    SELECT 'GOOG' Symbol UNION ALL
    SELECT 'GOOG' Symbol UNION ALL
    SELECT 'YHOO' Symbol UNION ALL
    SELECT 'T' Symbol
)
SELECT * INTO #stocks FROM Stocks;
--SELECT * FROM #stocks

ROW_NUMBER()

This will assigns unique numbers to each row within the PARTITION given to the ORDER BY clause. SQL Server require an explicit ORDER BY clause in the OVER() clause for string data type. ORDER BY is not mandatory in monetary columns (INT, DECIMAL, FLOAT …).

SELECT Symbol, ROW_NUMBER() OVER(ORDER BY Symbol) [ROW_NUMBER]
FROM #stocks

RANK()

This behaves like ROW_NUMBER(), except that “equal” rows are ranked the same. If we substitute RANK() from previous query:

SELECT Symbol, RANK() OVER(ORDER BY Symbol) [RANK]
FROM #stocks

As you can see, we have gaps between different ranks. We can avoid those gaps by using following;

DENSE_RANK()

DENSE_RANK() is a rank with no gaps, i.e. it is “dense”. We can write:

SELECT Symbol, DENSE_RANK() OVER(ORDER BY Symbol) [DENSE_RANK]
FROM #stocks

To get a good understanding of these three ranking functions is to see them all in action side-by-side. Run this query

SELECT
    SYMBOL,
    ROW_NUMBER() OVER(ORDER BY Symbol) [ROW_NUMBER],
    RANK() OVER(ORDER BY Symbol) [RANK],
    DENSE_RANK() OVER(ORDER BY Symbol) [DENSE_RANK]
FROM #stocks

Sometimes we don’t have order by column and we simple want to return row numbers using Row_Number function. Here is the same query with changes;

SELECT
    SYMBOL,
    ROW_NUMBER() OVER(ORDER BY Symbol) [ROW_NUMBER],
    RANK() OVER(ORDER BY Symbol) [RANK],
    DENSE_RANK() OVER(ORDER BY Symbol) [DENSE_RANK]
FROM #stocks
SELECT
	SYMBOL,
    ROW_NUMBER() OVER(ORDER BY (SELECT 1)) [ROW_NUMBER],
    RANK() OVER(ORDER BY (SELECT 1)) [RANK],
    DENSE_RANK() OVER(ORDER BY (SELECT 1)) [DENSE_RANK]
FROM #stocks

If you compare this result with earlier, you can see that RANK and DENSE_Rank functions doesn’t like the constant, so the values are 1 in respective columns.

You can use any literal value in Order by clause;

order by (select 0)
order by (select 1)
order by (select null)
order by (select 'test')

The above means that when you are using constant ordering is not performed by query optimizer.

Resources

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

https://stackoverflow.com/questions/44105691/row-number-without-order-by