David's Blog

What if you want to have the output from a stored procedure written to a temporary table, but you don’t want to have to worry about defining the temp table beforehand? Unfortunately, SQL Server doesn’t have an EXECUTE […]INTO [#temp_table] syntax, or similar. However, as a workaround, you can use SELECT INTO and OPENQUERY (looping-back to the same server) to achieve the desired result:

SELECT *
      INTO #who
      FROM OPENQUERY(YOUR_SERVER_NAME, ‘EXEC sp_who’)

You’ll need to define your server as a linked server for the above to work.

This technique is also a useful way of figuring out the data-types returned by a particular stored procedure:

SELECT TOP 0 *
      INTO #who
      FROM OPENQUERY(YOUR_SERVER_NAME, ‘EXEC sp_who’)

EXEC tempdb..sp_columns ‘#who’

There are occasions when it’s useful to have a function that returns a sequence. Here’s the function that I use for this purpose:

CREATE FUNCTION [dbo].[sequence_generator] (@iterations INT, @base INT, @increment SMALLINT)
    RETURNS TABLE
    WITH SCHEMABINDING
AS RETURN
WITH [seq] AS (
    SELECT d = ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
        UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
        UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
        UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
)
    SELECT TOP(@iterations) i = (@base) + ((ROW_NUMBER() OVER(ORDER BY s1.d) - 1) * @increment)
        FROM [seq] s1, [seq] s2, [seq] s3, [seq] s4, [seq] s5, [seq] s6, [seq] s7, [seq] s8

To get the first 100 rows of a zero-based sequence with +1 increments:

SELECT *
    FROM [dbo].[sequence_generator](100, 0, 1)

I’ll be referring back to this function in future posts.