USE [AutomationSandboxDB]
GO

/****** Object:  UserDefinedFunction [dbo].[StringFormat]    Script Date: 3/5/2020 7:43:04 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Michael J. Horvath
-- Create date: 03/04/2020
-- Description:	This function will format a string similar to the string.Format() function in C#.
--              The only difference is that its placeholders for fields are positionally identified and
--              use the syntax {?} for each field to be replaced with parameters.  The parameters are
--              also in a string delimited by commas eg. 'parameter1,parameter2,parameter3'.
--
--              The format specifier string also allows carriage return line feed with \n, tab with \t and
--              should you want to include in the result string the delimiter value without replacing it with
--              a parameter from the parameter list string escape it with \{?}.
--
--              Updated 03/05/2020 to use optional parameters for the parameter list to make a more natural appearing
--              list of parameters.  Now this is what a call to the function looks like: SELECT dbo.StringFormat('One: {?}\nTwo: {?}',@One,@Two)
--
--              Updated 03/06/2020 to use {{ or }} to escape curly braces same as string.Format's functionality.
-- =============================================
CREATE FUNCTION [dbo].[StringFormat]
(
	-- Add the parameters for the function here
	@FormatSpecifierString varchar(max),
	@Parameter0 sql_variant = NULL,
	@Parameter1 sql_variant = NULL,
	@Parameter2 sql_variant = NULL,
	@Parameter3 sql_variant = NULL,
	@Parameter4 sql_variant = NULL,
	@Parameter5 sql_variant = NULL,
	@Parameter6 sql_variant = NULL,
	@Parameter7 sql_variant = NULL,
	@Parameter8 sql_variant = NULL,
	@Parameter9 sql_variant = NULL
)
RETURNS varchar(max)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultString varchar(max) = ''

	DECLARE @ParameterListTable TABLE ([ArrayIndex] int identity(1,1) NOT NULL, [Item] sql_variant)

	INSERT INTO @ParameterListTable (Item) VALUES (@Parameter0),
		(@Parameter1),
		(@Parameter2),
		(@Parameter3),
		(@Parameter4),
		(@Parameter5),
		(@Parameter6),
		(@Parameter7),
		(@Parameter8),
		(@Parameter9);

	DECLARE @ParameterListString varchar(max) = ''

	DECLARE @ParameterCount int = 10

	IF @Parameter9 is NULL
	BEGIN
		SET @ParameterCount = @ParameterCount - 1

		IF @Parameter8 is NULL
		BEGIN
			SET @ParameterCount = @ParameterCount - 1

			IF @Parameter7 is NULL
			BEGIN
				SET @ParameterCount = @ParameterCount - 1

				IF @Parameter6 is NULL
				BEGIN
					SET @ParameterCount = @ParameterCount - 1

					IF @Parameter5 is NULL
					BEGIN
						SET @ParameterCount = @ParameterCount - 1

						IF @Parameter4 is NULL
						BEGIN
							SET @ParameterCount = @ParameterCount - 1

							IF @Parameter3 is NULL
							BEGIN
								SET @ParameterCount = @ParameterCount - 1

								IF @Parameter2 is NULL
								BEGIN
									SET @ParameterCount = @ParameterCount - 1

									IF @Parameter1 is NULL
									BEGIN
										SET @ParameterCount = @ParameterCount - 1

										IF @Parameter0 is NULL
										BEGIN
											SET @ParameterCount = @ParameterCount - 1
										END
									END
								END
							END
						END
					END
				END
			END
		END
	END

	WHILE @ParameterCount <> 0
	BEGIN
		SET @ParameterListString = replace(COALESCE((SELECT CONVERT(varchar(max),Item) FROM @ParameterListTable WHERE [ArrayIndex] = @ParameterCount),''),',',char(3)) + ',' + @ParameterListString;

		SET @ParameterCount = @ParameterCount - 1
	END

    IF LEN(@ParameterListString) > 0
	BEGIN
		SET @ParameterListString = SUBSTRING(@ParameterListString,1,LEN(@ParameterListString)-1);
	END

	DECLARE @UpperBoundFormatSpecifier int = 0
	DECLARE @UpperBoundParameters int = 0
	DECLARE @BuildStringIndex int = 1
	DECLARE @valueInt int

	DECLARE @FormatPart varchar(max)
	DECLARE @ParameterPart varchar(max)

	SET @FormatSpecifierString = replace(@FormatSpecifierString, '{{', char(2));
	SET @FormatSpecifierString = replace(@FormatSpecifierString, '}}', char(4));

	SELECT @UpperBoundFormatSpecifier = COUNT(Item) FROM dbo.SplitStrings(@FormatSpecifierString, '{?}');
	SELECT @UpperBoundParameters = COUNT(Item) FROM dbo.SplitStrings(@ParameterListString, ',');

	IF @UpperBoundFormatSpecifier = 1 AND @UpperBoundParameters = 1 AND LEN(@ParameterListString) = 0
	BEGIN
		SET @ResultString = replace(@FormatSpecifierString,char(2),'{');
		SET @ResultString = replace(@ResultString,char(4),'}');
		SET @ResultString = replace(@ResultString,char(3),',');
		SET @ResultString = replace(@ResultString,'\n',char(10));
		SET @ResultString = replace(@ResultString,'\t',char(9));

		RETURN @ResultString;
	END
	ELSE IF @UpperBoundFormatSpecifier = 1 AND @UpperBoundParameters = 1 AND LEN(@ParameterListString) <> 0
	BEGIN
		-- Throw not allowed in a user function so cause an error by attempting an invalid cast operation.
		SET @valueInt = cast('Here is the real ERROR: "The format specifier placeholder ({?}) count and parameter list count do not match!' as int);
	END

	IF @UpperBoundFormatSpecifier <> @UpperBoundParameters + 1
	BEGIN
		-- Throw not allowed in a user function so cause an error by attempting an invalid cast operation.
		SET @valueInt = cast('Here is the real ERROR: "The format specifier placeholder ({?}) count and parameter list count do not match!' as int);
	END

	WHILE @BuildStringIndex <= @UpperBoundParameters
	BEGIN
	    SELECT @FormatPart = Item FROM dbo.SplitStrings(@FormatSpecifierString, '{?}') WHERE ArrayIndex = @BuildStringIndex;
	    SELECT @ParameterPart = Item FROM dbo.SplitStrings(@ParameterListString, ',') WHERE ArrayIndex = @BuildStringIndex;

		SET @ResultString = @ResultString + @FormatPart + @ParameterPart;

		SET @BuildStringIndex = @BuildStringIndex + 1;
	END

	SELECT @FormatPart = Item FROM dbo.SplitStrings(@FormatSpecifierString, '{?}') WHERE ArrayIndex = @UpperBoundFormatSpecifier;

	SET @ResultString = @ResultString + @FormatPart

	SET @ResultString = replace(@ResultString,char(2),'{');
	SET @ResultString = replace(@ResultString,char(4),'}');
	SET @ResultString = replace(@ResultString,char(3),',');
	SET @ResultString = replace(@ResultString,'\n',char(10));
	SET @ResultString = replace(@ResultString,'\t',char(9));

	-- Return the result of the function
	RETURN @ResultString

END

GO