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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted April 11, 2013

WEBINAR:
On-Demand

How to Help Your Business Become an AI Early Adopter


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











×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.