Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted April 11, 2013

Unpack an Integer with SQL Server Recursion - Page 2

(SP1 SP_factorial)

USE [master]
 
IF OBJECT_ID('dbo.SP_factorial') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.SP_factorial
END
GO
 
CREATE PROCEDURE [dbo].[SP_factorial]
    @param INT,
    @fact  BIGINT OUTPUT
AS
BEGIN
    DECLARE @prev_param    INT
 
    IF  (@param > 20) OR
       (@param < 0)                  --  Return NULL if @param
                                     --  exceeds 21 or @param
                                      --  is negative
    BEGIN
       SET @fact = NULL
       RETURN
    END
 
    IF (@param = 0)
    BEGIN                            --  Base case
       SELECT @fact = 1
    END
    ELSE
    BEGIN                            --  Recursive call
 
       SET @prev_param = @param - 1 --  Use @prev_param as the recursive variable
                                     --  because subtracting one from @param will
                                     --  change its initial value, and the first
                                     --  recursive step with that value will never
                                     --  happen
 
       EXEC SP_factorial @prev_param, @fact OUTPUT
       SET @fact = @param * @fact
    END
END
GO

 

FN_1 (FN_factorial)

USE [master]
 
IF OBJECT_ID('FN_factorial') IS NOT NULL
BEGIN
    DROP FUNCTION FN_factorial
END
GO
 
CREATE FUNCTION FN_factorial
( @param BIGINT )
RETURNS BIGINT
 
AS
BEGIN
    IF (@param > 20) OR (@param < 0) --  Return NULL if @param
                                     --  exceeds 21 or @param
                                    --  is negative.
 
    BEGIN
       RETURN NULL
    END
 
    IF (@param > 1)                     --  Recursive call.
    BEGIN
       SET @param = @param * dbo.FN_factorial(@param - 1)
    END
    ELSE
    BEGIN                           --  Base case.
       SET @param = 1
    END
 
    RETURN @param
 
END

 

FN_2 (FN_CTE_factorial)

USE [master]
 
IF OBJECT_ID('dbo.FN_CTE_factorial') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FN_CTE_factorial
END
GO
 
CREATE FUNCTION [dbo].[FN_CTE_factorial] ( @param BIGINT )
RETURNS @fact_vals TABLE
(
    param_val  BIGINT,
    fact       BIGINT
)
AS
BEGIN
 
    IF (@param > 20) OR (@param < 0) --  Return the value if @param
                                     --  exceeds 21 or @param
                                     --  is negative
 
    BEGIN
       INSERT INTO @fact_vals   (param_val, fact)
       VALUES                (NULL, NULL)
       RETURN
    END
 
    ;WITH CTE_factorial (param_val, fact) AS
    (
       SELECT CAST(0 AS BIGINT) AS 'param_val', CAST(1 AS BIGINT) AS 'fact'
 
       UNION ALL
 
       SELECT (param_val + 1) AS 'param_val',
               (param_val + 1) * fact AS 'fact'
       FROM   CTE_factorial C
       WHERE  param_val < @param
    )
 
    INSERT INTO @fact_vals
    SELECT param_val, fact
    FROM CTE_factorial
    OPTION (MAXRECURSION 100)
 
    RETURN
 
END
 
GO

 

PNG_1 (PNG file 1)

multiselect listbox


SP_2 (SP_parseparam_output_varchar)

 
	USE [master] 
	GO 
	  
	IF OBJECT_ID('dbo.SP_parseparam_output_varchar') IS NOT NULL 
	BEGIN 
	DROP PROCEDURE dbo.SP_parseparam_output_varchar 
	END 
	GO 

CREATE PROCEDURE [dbo].[SP_parseparam_output_varchar]
    @param BIGINT,                        --  BIGINT because @PARAM might be a number "close" to
                                         --  and greater than ((2 ^ 31) - 1). A number like this
                                         --  would exceed the INT datatype limit and crash the SP.
 
    @parse_string NVARCHAR(220) OUTPUT   --  LEN(string for (2 ^ 31 - 1)) = 219.
AS
BEGIN
    DECLARE @component BIGINT
 
    IF  (@param > (POWER(2.0, 31) - 1)) OR   --  Simply return a NULL if the @param exceeds the INT
        (@param < 0)                     --  limit or @param < 0. Use 2.0 instead of 2 for the
                                              --  base to prevent overflow issues when the exponent
                                              --  is 31. 
    BEGIN
        SET @parse_string = NULL
        RETURN
    END
 
    IF (@param = 0)                          --  Base case.
    BEGIN
        IF (LEN(@parse_string) = 0)          --  Handle case where @param = 0.
        BEGIN
            SET @parse_string = '(0)'
        END
        ELSE
        BEGIN
            SET @parse_string = '(' + LEFT(@parse_string, LEN(@parse_string) - 1) + ')'
        END
    END
    ELSE
    BEGIN                                    --  Recursive call.
        SET     @component = POWER(2.0, FLOOR(LOG(@param, 2)))
        SET     @parse_string += CAST(@component AS NVARCHAR(25)) + ', '
        SET     @param -= @component
        EXEC    SP_parseparam_output_varchar @param, @parse_string OUTPUT
    END
 
END

 

FN_3 (FN_parseparam_TV_four_cols)

USE [master]
 
IF OBJECT_ID('dbo.FN_parseparam_TV_four_cols') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FN_parseparam_TV_four_cols
END
GO
 
CREATE FUNCTION [dbo].[FN_parseparam_TV_four_cols] ( @param BIGINT )
RETURNS @parsed_vals TABLE
(
    param_val                            BIGINT,
    largest_exp_of_two_in_param_val          INT,
    component                            BIGINT,
    new_param_val                            BIGINT
)
AS
BEGIN
 
    DECLARE @running_sum                     BIGINT
    DECLARE @parsed_val                      BIGINT
    DECLARE @largest_exp_of_two_in_param_val     INT
 
    IF  (
            (@param > (POWER(2.0, 47) - 1))  --  Because the LOG function will not work reliably in
                 OR                       --  this SP for all numbers exceeding (POWER(2.0, 47) - 1),
            (@param < 0)                 --  simply return one row with NULL values if the @param
        )                                --  exceeds this value, or if @param < 0. Use 2.0 instead
                                         --  of 2 for the base to prevent overflow issues when the
                                         --  exponent exceeds 31. This means that this SP can handle
                                         --  parameters composed of at most 47 base-two integers,
                                         --  not 63 - the biggest BIGINT integer is ((2 ^ 63) - 1).
    BEGIN
        INSERT INTO @parsed_vals (param_val, largest_exp_of_two_in_param_val, component, new_param_val)
        VALUES                   (NULL, NULL, NULL, NULL)
        RETURN
    END
    ELSE IF (@param = 0)         --  Return one row with zero(es) if @param = 0.
    BEGIN
        INSERT INTO @parsed_vals (param_val, largest_exp_of_two_in_param_val, component, new_param_val)
        VALUES                   (0, 0, 0, 0)
        RETURN
    END
 
    --  Get the first base-two component of @param.
 
    SET @largest_exp_of_two_in_param_val = FLOOR(LOG(@param, 2.0))
 
    ;WITH CTE_parseparam (param_val, largest_exp_of_two_in_param_val, component, new_param_val) AS
    (
        --  In the POWER functions, use 2.0 instead of 2 for the
        --  base to prevent overflow issues when the exponent
        --  exceeds 30.
 
        --  Base case.
 
        SELECT  CAST(@param AS BIGINT)                   AS 'param_val',
                    @largest_exp_of_two_in_param_val AS 'largest_exp_of_two_in_param_val',
                    CAST(POWER(2.0, @largest_exp_of_two_in_param_val) AS BIGINT)
                        AS 'component',
                     CAST((@param - POWER(2.0, @largest_exp_of_two_in_param_val)) AS BIGINT)
                        AS 'new_param_val'
        UNION ALL
 
        --  Recursive call.
 
        SELECT  CAST((C.param_val - C.component) AS BIGINT)       AS 'param_val',
                    CAST(FLOOR(LOG(C.new_param_val, 2)) AS INT)   AS 'largest_exp_of_two_in_param_val',
                    CAST(POWER(2.0, (FLOOR(LOG((C.new_param_val), 2)))) AS BIGINT)    AS 'component',
                     CAST((C.new_param_val) - POWER(2.0, FLOOR(LOG(C.new_param_val, 2))) AS BIGINT)
                         AS 'new_param_val'
        FROM    CTE_parseparam C
        WHERE   new_param_val > 0    --  The recursion stops when
                                 --  new_param_val hits zero.
    )
 
    INSERT INTO @parsed_vals
    SELECT param_val, largest_exp_of_two_in_param_val, component, new_param_val
    FROM CTE_parseparam
 
    RETURN
END
GO

FN_4 (FN_parseparam_TV_component_col)

USE [master]
 
IF OBJECT_ID('dbo.FN_parseparam_TV_component_col') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FN_parseparam_TV_component_col
END
GO
 
CREATE FUNCTION [dbo].[FN_parseparam_TV_component_col] ( @param BIGINT )
RETURNS @parsed_vals TABLE
(
    component   BIGINT
)
AS
BEGIN
 
    DECLARE @running_sum                     BIGINT
    DECLARE @parsed_val                      BIGINT
    DECLARE @largest_pwr_of_two_in_param_val INT
 
    IF  (
            (@param > (POWER(2.0, 47) - 1))  --  Because the LOG function will not work reliably in
                 OR                           --  this SP for all numbers exceeding (POWER(2.0, 47) - 1),
            (@param < 0)                     --  simply return one row with NULL values if the @param
        )                                    --  exceeds this value, or if @param < 0. Use 2.0 instead
                                             --  of 2 for the base to prevent overflow issues when the
                                             --  exponent exceeds 31. This means that this SP can handle
                                             --  parameters composed of at most 47 base-two integers,
                                             --  not 63 - the biggest BIGINT integer is ((2 ^ 63) - 1).
    BEGIN
        INSERT INTO @parsed_vals (component) VALUES (NULL)
        RETURN
    END
    ELSE IF (@param = 0)                     --  Return zero if @param = 0.
    BEGIN
        INSERT INTO @parsed_vals (component) VALUES (0)
        RETURN
    END
 
    --  Get the first base-two component of @param.
 
    SET @largest_pwr_of_two_in_param_val = FLOOR(LOG(@param, 2.0))
 
    ;WITH CTE_parseparam (param_val, largest_pwr_of_two_in_param_val, component, new_param_val) AS
    (
        --  In the POWER functions, use 2.0 instead of 2 for the
        --  base to prevent overflow issues when the exponent
        --  exceeds 30.
 
        SELECT  CAST(@param AS BIGINT)                       AS 'param_val',
                 @largest_pwr_of_two_in_param_val         AS 'largest_pwr_of_two_in_param_val',
                 CAST(POWER(2.0, @largest_pwr_of_two_in_param_val) AS BIGINT)
                     AS 'component',
                 CAST((@param - POWER(2.0, @largest_pwr_of_two_in_param_val)) AS BIGINT)
                    AS 'new_param_val'
        UNION ALL
        SELECT  CAST((C.param_val - C.component) AS BIGINT)   AS 'param_val',
                 CAST(FLOOR(LOG(C.new_param_val, 2)) AS INT)   AS 'largest_pwr_of_two_in_param_val',
                 CAST(POWER(2.0, (FLOOR(LOG((C.new_param_val), 2)))) AS BIGINT)
                     AS 'component',
                 CAST((C.new_param_val) - POWER(2.0, FLOOR(LOG(C.new_param_val, 2))) AS BIGINT)
                     AS 'new_param_val'
        FROM    CTE_parseparam C
        WHERE   new_param_val > 0    --  The recursion stops when
                                 --  new_param_val hits zero.
    )
 
    INSERT INTO @parsed_vals
    SELECT component
    FROM CTE_parseparam
 
    RETURN
END
GO

FN_5 (FN_convert_TV_to_varchar)

USE [master]
 
IF OBJECT_ID('dbo.FN_convert_TV_to_varchar') IS NOT NULL
BEGIN
   DROP FUNCTION dbo.FN_convert_TV_to_varchar
END
GO
 
-- Use this function for integers between ((2 ^ 31) - 1) and ((2.0, 47) - 1),
-- because stored procedure
--
--    SP_parseparam_output_string_2012
--
-- will handle integers up to ((2 ^ 31) - 1), and function
--
--    FN_parseparam_table_variable_component_col_2012
--
-- will handle integers up to ((2.0 ^ 47) - 1). Note that this function
-- will handle ALL integers from 0 to ((2.0 ^ 47) - 1).
 
CREATE FUNCTION [dbo].[FN_convert_TV_to_varchar] ( @param BIGINT )
RETURNS VARCHAR(500)
 
AS
BEGIN
   DECLARE   @local_TV TABLE ( component BIGINT )
   DECLARE   @component_list varchar(500)
   SET       @component_list = ''
 
   INSERT INTO @local_TV (component)
   SELECT component FROM FN_parseparam_TV_component_col (@param)
 
   -- Concatenate @component_list with the
   -- values in @local_TV returned by the
   -- SELECT statement, row by row.
 
   SELECT    @component_list = COALESCE(@component_list + ', ', '') + 
             CAST(component as varchar(25))
   FROM      @local_TV
   ORDER BY  component
 
   SET @component_list = '(' + SUBSTRING(@component_list, 3, 500) + ')'
 
   RETURN @component_list
 
END

Frank Solomon handles all aspects of SQL Server 2005 / 2008 / 2012 development, including stored procedures, functions, jobs, and general database design. He handles VB.net, C#, and ASP.net development as well. Additionally, he handles technical writing and he has an iApp on sale in the iTunes store (BSD by Panda Cub Productions). He is looking for a SQL Server / front-end development opportunity in the Los Angeles area. See more at www.linkedin.com/in/franksolomonand reach him at fbs.author@gmail.com



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date