Useful Microsoft SQL Server Function And Stored Procedure

Dikarenakan masih banyak client yang menggunakan Microsoft SQL Server 2012, maka pada artikel dibuatkan Function dan Stored Procedure yang kompatibel dengan kebutuhan tersebut. Artikel ini akan diupdate secara berkala, silahkan like, comment dan bookmark artikel ini.

Version

• version-1 (2021-01-28)

Function

Scalar-valued Functions

1. String_Trim

CREATE FUNCTION [dbo].[String_Trim] (
	@String varchar(MAX)
)
RETURNS varchar(MAX)
AS
BEGIN
	DECLARE @Result varchar(MAX);
	SET @Result = LTRIM(RTRIM(@String));
	RETURN @Result;
END

Table-valued Functions

1. String_Split

CREATE FUNCTION [dbo].[String_Split]
(
  @String varchar(MAX),
  @Separator varchar(255)
)
RETURNS @Result table (
	[Id] int identity(1,1),
	[Value] varchar(MAX)
)
AS  
BEGIN
	DECLARE @Count INT;
	SET @Count = 1;
	WHILE (CHARINDEX(@Separator, @String) > 0)
	BEGIN
		INSERT INTO @Result ([Value])
		SELECT [Data] = dbo.STRING_TRIM(SUBSTRING(@String, 1, CHARINDEX(@Separator, @String) - 1));

		SET @String = SUBSTRING(@String, CHARINDEX(@Separator, @String) + 1, LEN(@String));
		SET @Count = @Count + 1;
	END;
	
	INSERT INTO 
		@Result ([Value])
	SELECT 
		[Value] = dbo.STRING_TRIM(@String);
RETURN
END

Stored Procedure

1. sp_reset_identity

CREATE PROCEDURE [dbo].[sp_reset_identity]
	@TableName varchar(50),
	@Value bigint
AS
BEGIN
	SET @TableName = '[' + @TableName + ']';
    DBCC CHECKIDENT(@TableName, RESEED, @Value);
END