Sql-server – alternatives to passing temp table as a parameter to a stored procedure

dynamic-sqlparametersql serverstored-procedurestable-valued-parameters

some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure:

USE MASTER
GO
ALTER PROCEDURE dbo.sp_getDBSpace
    @Radhe sysname=null,
    @system_only BIT = NULL ,
    @user_only BIT = NULL ,
    @database_list NVARCHAR(MAX) = NULL ,
    @exclude_list NVARCHAR(MAX) = NULL ,
    @recovery_model_desc NVARCHAR(120) = NULL ,
    @compatibility_level TINYINT = NULL ,
    @state_desc NVARCHAR(120) = N'ONLINE' ,
    @is_read_only BIT = 0 ,
    @is_auto_close_on BIT = NULL ,
    @is_auto_shrink_on BIT = NULL ,
    @is_broker_enabled BIT = NULL 
AS

BEGIN

SET NOCOUNT ON
SET XACT_ABORT ON
SET DEADLOCK_PRIORITY NORMAL;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @SQL NVARCHAR(MAX)


                    SET @sql = N'USE [?]

                    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
                    ;with radhe as (

                    SELECT 
                    Servername   = @@servername,
                    DatabaseName = DB_NAME(), 

                    a.FILE_ID, 
                    a.type, -- 0 is log and 1 is data

                    [Drive] = LEFT(UPPER(a.physical_name), 1), 

                    [FILE_SIZE_GB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000/1024.000, 2)), 

                    [SPACE_USED_GB] = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.NAME, ''SpaceUsed'') / 128.000/1024.000, 2)), 

                    [FREE_SPACE_GB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.NAME, ''SpaceUsed'')) / 128.000/1024.000, 2)), 

                    a.NAME, 
                    a.PHYSICAL_NAME AS [FILENAME]

                    FROM sys.database_files a
                    )

                        insert into ' + @Radhe + N'([ServerName],[DatabaseName],[Drive],[FILE_ID],[FILE_SIZE_GB],[SPACE_USED_GB],[FREE_SPACE_GB],[NAME],[FILENAME],[TOTAL_DATABASE_SIZE])
                        SELECT 
                                             [ServerName]           
                                            ,[DatabaseName]         
                                            ,[Drive]                
                                            ,[FILE_ID]              
                                            ,[FILE_SIZE_GB]         
                                            ,[SPACE_USED_GB]        
                                            ,[FREE_SPACE_GB]        
                                            ,[NAME]                 
                                            ,[FILENAME]    
                                            ,TOTAL_DATABASE_SIZE      = SUM([FILE_SIZE_GB] ) OVER (PARTITION BY A.DATABASENAME )
                                            --,SPACE_USED               = SUM([SPACE_USED_GB] ) OVER (PARTITION BY A.DATABASENAME )
                                            --,TOTAL_FREE_LOG_SPACE_GB  = SUM(CASE WHEN A.TYPE = 0 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME )
                                            --,TOTAL_FREE_DATA_SPACE_GB = SUM(CASE WHEN A.TYPE = 1 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME )
                         FROM radhe a


                            '

             exec sp_foreachdb 
                    @system_only = @system_only,
                    @user_only = @user_only,
                    @database_list = @database_list,
                    @exclude_list = @exclude_list,
                    @recovery_model_desc = @recovery_model_desc,
                    @compatibility_level = @compatibility_level,
                    @state_desc = @state_desc,
                    @is_read_only = @is_read_only ,
                    @is_auto_close_on = @is_auto_close_on,
                    @is_auto_shrink_on = @is_auto_shrink_on ,
                    @is_broker_enabled = @is_broker_enabled
                  ,@command=@SQL 

END
GO

And here is how I used to call this procedure:

    IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL 
    DROP TABLE #Radhe
    GO
    CREATE TABLE #Radhe( 
    [ServerName]           NVARCHAR(128)                        NULL,
    [DatabaseName]         NVARCHAR(128)                        NULL,
    [Drive]                NVARCHAR(1)                          NULL,
    [FILE_ID]              INT                                  NOT NULL,
    [FILE_SIZE_GB]         DECIMAL(12,2)                        NULL,
    [SPACE_USED_GB]        DECIMAL(12,2)                        NULL,
    [FREE_SPACE_GB]        DECIMAL(12,2)                        NULL,
    [NAME]                 SYSNAME                              NOT NULL,
    [FILENAME]             NVARCHAR(260)                        NULL,
    [TOTAL_DATABASE_SIZE]  DECIMAL(38,2)                        NULL)

     declare @db_list NVARCHAR(MAX)


     SELECT @db_list  = STUFF((
                                SELECT ', ' + name FROM sys.databases
                                WHERE name NOT IN ('DBA','TABLEBACKUPS','MASTER','MSDB','MODEL','TEMPDB')
                                FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

     --just to check the list                                           
     --exec sp_foreachdb @database_list = @db_list
              --       ,@command='use ?; print db_name() + char(13)'   

     exec sp_getDBSpace @Radhe = '#Radhe'
                       ,@database_list = @db_list
                       ,@exclude_list = 'DBA,TABLEBACKUPS,MASTER,MSDB,MODEL,TEMPDB'

     select * from #Radhe

Question:

Although it works is there a more elegant way to get this done by doing something else rather than passing a temp table name as a parameter?

I need the temp table to be available after the call of the stored procedure.

Inside the stored procedure, I need to insert data into that temp table.

is there any other way of getting this done, without using a temp table as a parameter as I did?

there is a similar question here.

Some links:

— How to restore a database, ignoring the free space within each file?

— Generate INSERT list but only for non-nullable columns

— Determining Drive Letters in Use by SQL Server Databases

— https://www.mssqltips.com/sqlservertip/3048/determining-drive-letters-in-use-by-sql-server-databases/

— PROCEDURE sp_foreachdb

— https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_foreachdb.sql

Best Answer

You can create the temp table inside sp_getDbSpace and then return a single resultset.

Like this:

CREATE OR ALTER PROCEDURE dbo.sp_getDBSpace
    @Radhe sysname=null,
    @system_only BIT = NULL ,
    @user_only BIT = NULL ,
    @database_list NVARCHAR(MAX) = NULL ,
    @exclude_list NVARCHAR(MAX) = NULL ,
    @recovery_model_desc NVARCHAR(120) = NULL ,
    @compatibility_level TINYINT = NULL ,
    @state_desc NVARCHAR(120) = N'ONLINE' ,
    @is_read_only BIT = 0 ,
    @is_auto_close_on BIT = NULL ,
    @is_auto_shrink_on BIT = NULL ,
    @is_broker_enabled BIT = NULL 
AS

BEGIN

SET NOCOUNT ON
SET XACT_ABORT ON
SET DEADLOCK_PRIORITY NORMAL;

DECLARE @SQL NVARCHAR(MAX)

   CREATE TABLE #Radhe( 
    [ServerName]           NVARCHAR(128)                        NULL,
    [DatabaseName]         NVARCHAR(128)                        NULL,
    [Drive]                NVARCHAR(1)                          NULL,
    [FILE_ID]              INT                                  NOT NULL,
    [FILE_SIZE_GB]         DECIMAL(12,2)                        NULL,
    [SPACE_USED_GB]        DECIMAL(12,2)                        NULL,
    [FREE_SPACE_GB]        DECIMAL(12,2)                        NULL,
    [NAME]                 SYSNAME                              NOT NULL,
    [FILENAME]             NVARCHAR(260)                        NULL,
    [TOTAL_DATABASE_SIZE]  DECIMAL(38,2)                        NULL)


    DECLARE @SQL nvarchar(max) @sql = N'USE [?]

    ;with radhe as (

    SELECT 
    Servername   = @@servername,
    DatabaseName = DB_NAME(), 
    a.FILE_ID, 
    a.type, -- 0 is log and 1 is data
    [Drive] = LEFT(UPPER(a.physical_name), 1), 
    [FILE_SIZE_GB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000/1024.000, 2)), 
    [SPACE_USED_GB] = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.NAME, ''SpaceUsed'') / 128.000/1024.000, 2)), 
    [FREE_SPACE_GB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.NAME, ''SpaceUsed'')) / 128.000/1024.000, 2)), 
    a.NAME, 
    a.PHYSICAL_NAME AS [FILENAME]

    FROM sys.database_files a
    )

        insert into #Radhe([ServerName],[DatabaseName],[Drive],[FILE_ID],[FILE_SIZE_GB],[SPACE_USED_GB],[FREE_SPACE_GB],[NAME],[FILENAME],[TOTAL_DATABASE_SIZE])
        SELECT 
                                [ServerName]           
                            ,[DatabaseName]         
                            ,[Drive]                
                            ,[FILE_ID]              
                            ,[FILE_SIZE_GB]         
                            ,[SPACE_USED_GB]        
                            ,[FREE_SPACE_GB]        
                            ,[NAME]                 
                            ,[FILENAME]    
                            ,TOTAL_DATABASE_SIZE      = SUM([FILE_SIZE_GB] ) OVER (PARTITION BY A.DATABASENAME )
                            --,SPACE_USED               = SUM([SPACE_USED_GB] ) OVER (PARTITION BY A.DATABASENAME )
                            --,TOTAL_FREE_LOG_SPACE_GB  = SUM(CASE WHEN A.TYPE = 0 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME )
                            --,TOTAL_FREE_DATA_SPACE_GB = SUM(CASE WHEN A.TYPE = 1 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME )
            FROM radhe a


            '

             exec sp_foreachdb 
                    @system_only = @system_only,
                    @user_only = @user_only,
                    @database_list = @database_list,
                    @exclude_list = @exclude_list,
                    @recovery_model_desc = @recovery_model_desc,
                    @compatibility_level = @compatibility_level,
                    @state_desc = @state_desc,
                    @is_read_only = @is_read_only ,
                    @is_auto_close_on = @is_auto_close_on,
                    @is_auto_shrink_on = @is_auto_shrink_on ,
                    @is_broker_enabled = @is_broker_enabled
                  ,@command=@SQL 

        select * from #Radhe
END

Related Solutions

Sql-server – Table-Valued Parameter as Output parameter for stored procedure

No, unfortunately table value parameters are read-only and input only. This topic in general is covered very well in How to Share Data between Stored Procedures, which presents all the alternatives. My recommendation would be to use a #temp table.

Sql-server – Passing in the table into a stored procedure

Instead of creating views, why not make an inline table-valued function - effectively a parameterized view. You basically get all the benefits of views AND parameters.

Another thing you can do in your TVF is something like this:

SELECT yourtable.*
FROM yourtable
INNER JOIN ranges
    ON yourtable.date BETWEEN ranges.start AND ranges.end
WHERE ranges.name = @range_name

Now your ranges have names (in a table where they can be easily managed without changing the schema) - this also fairly seamlessly handles multiple ranges with the same name, although if ranges overlap you can have duplicates due to the join matching both.

Also, please consider avoiding BETWEEN:

https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common

I strongly suggest that you reconsider your requirements and when asking questions here be sure to include more about your motivations, because when you bring in a question with a preconceived notion of what the solution is, you are likely to get a suboptimal solution. (like the dynamic SQL solutions given aren't going to easily work for your OR case, but that information about the problem space is buried in a comment from you)

From: http://sqlfiddle.com/#!6/a628a/1

CREATE TABLE LogData (
  Id INT IDENTITY NOT NULL
  ,Dt DATETIME NOT NULL
  ,Data VARCHAR(max) NOT NULL
);

CREATE TABLE Ranges (
  Id INT IDENTITY NOT NULL
  ,Name VARCHAR(50) NOT NULL
  ,DtStart DATETIME NOT NULL
  ,DtEnd DATETIME NOT NULL
);

CREATE TABLE Groups (
  Id INT IDENTITY NOT NULL
  ,Name VARCHAR(50) NOT NULL
);

CREATE TABLE RangeGroups (
  RangeId INT NOT NULL
  ,GroupId INT NOT NULL
);

INSERT INTO LogData (Dt, Data)
VALUES ('2011-11-23 11:00', 'before thanksgiving day')
       ,('2011-11-24 11:00', 'on thanksgiving day')
       ,('2011-12-24 11:00', 'on christmas eve')
       ,('2011-12-25 11:00', 'on christmas day')
       ,('2012-11-21 11:00', 'before thanksgiving day')
       ,('2012-11-22 11:00', 'on thanksgiving day')
       ,('2012-12-24 11:00', 'on christmas eve')
       ,('2012-12-25 11:00', 'on christmas day');

INSERT INTO Ranges (Name, DtStart, DtEnd)
VALUES ('THANKSGIVING2011', '2011-11-24', '2011-11-25')
       ,('CHRISTMASEVE2011', '2011-12-24', '2011-12-25')
       ,('CHRISTMASDAY2011', '2011-12-25', '2011-12-26')
       ,('BOXINGDAY2011', '2011-12-26', '2011-12-27')
       ,('NEWYEARSEVE2011', '2011-12-31', '2012-01-01')
       ,('NEWYEARSDAY2012', '2012-01-01', '2012-01-02')
       ,('THANKSGIVING2012', '2012-11-22', '2012-11-23')
       ,('CHRISTMASEVE2012', '2012-12-24', '2012-12-25')
       ,('CHRISTMASDAY2012', '2012-12-25', '2012-12-26')
       ,('BOXINGDAY2012', '2012-12-26', '2012-12-27')
       ,('NEWYEARSEVE2012', '2012-12-31', '2013-01-01')
       ,('NEWYEARSDAY2013', '2013-01-01', '2013-01-02');

INSERT INTO Groups (Name)
VALUES ('HOLIDAYS2011')
       ,('HOLIDAYS2012')
       ,('ANYCHRISTMAS');

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2011')
FROM Ranges WHERE Name LIKE '%2011';

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2012')
FROM Ranges WHERE Name LIKE '%2012';

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'ANYCHRISTMAS')
FROM Ranges WHERE Name LIKE 'CHRISTMAS%';


CREATE FUNCTION dbo.JustDoIt(@GroupName VARCHAR(50))
RETURNS TABLE
RETURN (
SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName
);

SELECT *
FROM dbo.JustDoIt('HOLIDAYS2011');

SELECT *
FROM dbo.JustDoIt('HOLIDAYS2012');

SELECT *
FROM dbo.JustDoIt('ANYCHRISTMAS');

This part:

SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName

Means that each log item will be joined to the ranges it fits in (by date), each range will join to the groups they are in, but only that group selected will be used.

Best Answer

Related Solutions

Sql-server – Table-Valued Parameter as Output parameter for stored procedure

Sql-server – Passing in the table into a stored procedure

Related Question