2012-10-24

SQL Server - Use table value parameter to make dynamic stored procedure

Before SQL Server provide User-Defined Table Types objects, I can't pass table value or dataset to stored procedure, I always input the string value to parameter, e.g. @Parameter1 = '1,2,3,4,5', then split it into temp table, or use other stored procedure/function to get dataset. This issue always bother me, especially I try to make a dynamic stored procedure.



Here is the case, every time I do the project, I need to use the company's calculating formula to get the result, but every project might base on different dataset, so I need to create a new stored procedure for each new project and modify calculation query to meet the requirement, then I start to think is it possible to create a stored procedure to deal with different dataset.

The raw dataset in the Excel looks as follows:

ID
C1
1
XXXX
2
XXXX
3
XXXX
4
XXXX
5
XXXX
6
XXXX
7
XXXX
8
XXXX
9
XXXX
10
XXXX

I found that it's very easy deal with the dataset which its row counts is even number in the SQL Server, I can use AVG(), ORDER BY, and sub-query to get result. But if I encounter the row counts is odd number, it's not very precisely calculate it in SQL Server, unless manually calculate in the Excel:

ID
C1
1
XXXX
2
XXXX
3
XXXX
4
XXXX
5
XXXX
6
XXXX
7
XXXX
8
XXXX
9
XXXX
10
XXXX
11
XXXX

I can get rough result if I still use the same query to calculate it, because the calculation will include complete single row value. Please see the following steps how to precisely calculate it with table value parameter in the stored procedure:

1.Create User-Defined Table type. The first column is sequence and incremental number, and the rest depend on how many columns I want to calculate.
USE [Target_DB]
GO
CREATE TYPE [dbo].[Table_Value_Name] AS TABLE
(
    [ID] [int] NOT NULL,
    [C1] [float] NULL,
    [C2] [float] NULL,
    [C3] [float] NULL,
    [C4] [float] NULL,
    [C5] [float] NULL,
    [C6] [float] NULL,
    [C7] [float] NULL,
    [C8] [float] NULL,
    [C9] [float] NULL,
    [C10] [float] NULL,
    [C11] [float] NULL,
    [C12] [float] NULL,
    [C13] [float] NULL,
    [C14] [float] NULL,
    [C15] [float] NULL,
    [C16] [float] NULL,
    [C17] [float] NULL,
    [C18] [float] NULL,
    [C19] [float] NULL,
    [C20] [float] NULL,
    [C21] [float] NULL,
    [C22] [float] NULL,
    [C23] [float] NULL,
    [C24] [float] NULL,
    [C25] [float] NULL,
    [C26] [float] NULL,
    [C27] [float] NULL,
    [C28] [float] NULL,
    [C29] [float] NULL,
    [C30] [float] NULL
)
GO

2.Create stored procedure. There are two parameters in this stored procedure, first one is table value variable created above, second one is used for controlling which range I want to calculate, or type ALL to return every range.
USE [Target_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================
CREATE PROCEDURE [dbo].[Stored_Procedure_Name]   
    @T dbo.Table_Value_Name READONLY,
    @Range VARCHAR(300)
AS
BEGIN   
    SET NOCOUNT ON;
   
    DECLARE @p                                  INT,
                      @counts                           INT,
                      @loop_i                           INT,
                      @bm_records                  FLOAT,
                      @bm_records_int             FLOAT,
                      @bm_records_decimal     FLOAT,
                      @bp_records                   FLOAT,
                      @bp_records_int             FLOAT,
                      @bp_records_decimal     FLOAT,
                      @sql_cmd                       NVARCHAR(MAX),
                      @column                         NVARCHAR(50)
    --============================================================
    --Split @Range variable into temp table
    IF RIGHT(@Range, 1) = ','
        SET @Range = SUBSTRING(@Range, 1, LEN(@Range)-1)
           
    DECLARE @range_tmp TABLE ( range_id INT )

    DECLARE @range_counts        INT,
                       @range_pos            INT

    SET        @range_counts        = 1
    SET        @range_pos            = 0

    WHILE CHARINDEX(',', @range, @range_pos) > 0
    BEGIN
        SET @range_pos        = CHARINDEX(',', @range, @range_pos) + 1
        SET @range_counts    = @range_counts + 1
    END

    SET @range_pos = 0

    WHILE @range_counts > 0
    BEGIN
        IF @range_counts = 1 --Get the last item.
            INSERT INTO @range_tmp SELECT CONVERT(INT, RTRIM(LTRIM(SUBSTRING(@range, @range_pos, LEN(@range) - @range_pos + 1))))
        ELSE
            INSERT INTO @range_tmp SELECT CONVERT(INT, RTRIM(LTRIM(SUBSTRING(@range, @range_pos, CHARINDEX(',', @range, @range_pos)- @range_pos))))
               
        SET @range_pos = CHARINDEX(',', @range, @range_pos) + 1       
        SET @range_counts = @range_counts - 1       
    END
--============================================================
    --Retrieve column counts/structure of dbo.Table_Value_Name
    CREATE TABLE #tmp_C_P (C NVARCHAR(5))
   
    DECLARE @utcolumns TABLE (column_name NVARCHAR(30))
   
    INSERT INTO @utcolumns
    SELECT  sac.name
    FROM    [sys].[table_types] stt
                   JOIN [sys].[all_columns] sac
                      ON stt.type_table_object_id = sac.object_id
    WHERE    stt.user_type_id = TYPE_ID('Table_Value_Name')
   
    --SELECT * FROM @utcolumns
    SELECT    @counts = COUNT(*) - 1 FROM @utcolumns
    --SELECT @counts
   
    SELECT * INTO #tmp_P
    FROM @T
   
    SET @loop_i = 1
    SET @sql_cmd = ''

    --Use first row's value to control which column need to be calculated and returned
    WHILE @loop_i <= @counts
    BEGIN       
        SET @sql_cmd = @sql_cmd + 'IF (SELECT C'+CONVERT(NVARCHAR(2), @loop_i)+' FROM #tmp_P WHERE ID = 0) = 1 '
        SET @sql_cmd = @sql_cmd + 'INSERT INTO #tmp_C_P VALUES(''C'+CONVERT(NVARCHAR(2), @loop_i)+''') '       
        SET @loop_i = @loop_i + 1
    END
   
    --SELECT @sql_cmd
    EXEC(@sql_cmd)   
   
    SET @sql_cmd = ''
   
    --SELECT * FROM #tmp_P
    --SELECT * FROM #tmp_C_P
   
    SELECT @counts = COUNT(*) FROM #tmp_P WHERE ID > 0

    SET @p            = 5
   
    --This temp table help me to identify calculated base
    DECLARE @p_range TABLE
            (   p    INT,
                range_value    FLOAT,
                range_value_int    INT,
                range_value_decimal    FLOAT    )
 
    --SELECT * FROM @p_range
   
    DECLARE @RV        FLOAT,
            @RVI    INT,
            @RVD    FLOAT
 
    --The following section of statement, I just explain which method/function I used, and I also need to consider about the NULL value inside the dataset, I tried to simulate SQL Server, if encounter NULL value then don't include the row. If you have any questions, please let me know.   
--1. Identity which range I want to calculate, use IF...ELSE..., WHILE loop
--2. Create dynamic string for column structure of #tmp_P, use CURSOR, UNION ALL
--3. Ignore NULL value, use SUM(CASE WHEN ... IS NOT NULL THEN ... ELSE 0 END)
--4. Prevent divided by zero error, NULLIF(SUM(CASE WHEN...END), 0)

3.Demonstrate how to use this stored procedure.
DECLARE    @T1 AS dbo.Table_Value_Name
--This part is used for control how many columns need to be calculated, the rest won't return to the result, in this case are C26 to C30
INSERT INTO @T1
    (ID, C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, C13, C14, C15, C16, C17, C18, C19, C20, C21, C22, C23, C24, C25)
    VALUES(0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
     INSERT INTO @T1
    (ID, C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, C13, C14, C15, C16, C17, C18, C19, C20, C21, C22, C23, C24, C25)
    SELECT
            --ID is used to reorder the dataset in the stored procedure
            ROW_NUMBER() OVER (ORDER BY [Column_1] * [Column_2]),
            [Column_1],
            [Column_2],
            [Column_3],
            [Column_4],
            [Column_5],
            [Column_6],
            [Column_7],
            [Column_8],
            [Column_9],
            [Column_10],
            [Column_11],
            [Column_12],
            [Column_13],
            [Column_14],
            [Column_15],
            [Column_16],
            [Column_17],
            [Column_18],
            [Column_19],
            [Column_20],
            [Column_21],
            [Column_22],
            [Column_23],
            [Column_24],           
            [Column_25]   
 FROM  [Original_DataSet]

INSERT INTO [Target_DateSet]
       (    [Title]
            [Number_Of_DataSet],
            [Column_1],
            [Column_2],
            [Column_3],
            [Column_4],
            [Column_5],
            [Column_6],
            [Column_7],
            [Column_8],
            [Column_9],
            [Column_10],
            [Column_11],
            [Column_12],
            [Column_13],
            [Column_14],
            [Column_15],
            [Column_16],
            [Column_17],
            [Column_18],
            [Column_19],
            [Column_20],
            [Column_21],
            [Column_22],
            [Column_23],
            [Column_24],           
            [Column_25] )
    EXEC dbo.Stored_Procedure_Name @T1, 'Range_String'

No comments:

Post a Comment