(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)
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
» 
Comment and Contribute
