There are cases where we need to pass the input parameter to the stored procedure as a comma-separated string in order to avoid multiple db calls from happening. This helps in reducing the db calls thereby reducing the load on the db server.
The below function will split the given comma-separated string into integers and process the results.
IF EXISTS(SELECT name FROM sys.objects WHERE name='fnSplitBigInt' AND type='TF')
DROP FUNCTION [dbo].[fnSplitBigInt]
GO
CREATE FUNCTION [dbo].[fnSplitBigInt]
(
-- Add the parameters for the function here
@input nvarchar(4000)
)
RETURNS @retBigint TABLE
(
[Value] [bigint] NOT NULL
)
AS
BEGIN
DECLARE @bigint nvarchar(100)
DECLARE @pos int
SET @input = LTRIM(RTRIM(@input))+ ',' -- TRIMMING THE BLANK SPACES
SET @pos = CHARINDEX(',', @input, 1) -- OBTAINING THE STARTING POSITION OF COMMA IN THE GIVEN STRING
IF REPLACE(@input, ',', '') <> '' -- CHECK IF THE STRING EXIST FOR US TO SPLIT
BEGIN
WHILE @pos > 0
BEGIN
SET @bigint = LTRIM(RTRIM(LEFT(@input, @pos - 1))) -- GET THE 1ST INT VALUE TO BE INSERTED
IF @bigint <> ''
BEGIN
INSERT INTO @retBigint (Value)
VALUES (CAST(@bigint AS bigint))
END
SET @input = RIGHT(@input, LEN(@input) - @pos) -- RESETTING THE INPUT STRING BY REMOVING THE INSERTED ONES
SET @pos = CHARINDEX(',', @input, 1) -- OBTAINING THE STARTING POSITION OF COMMA IN THE RESETTED NEW STRING
END
END
RETURN
END
The following is an sample output result set of that function
SELECT * FROM dbo.fnSplitbigint('12345,87612,988473')
