(split) string with comma in SQL Server stored procedure (SQL COMMA SPLIT)

DECLARE @str VARCHAR(500) = '1,2,3'
CREATE TABLE #Temp (tDay VARCHAR(100),Tid BigInt)
WHILE LEN(@str) > 0
BEGIN
    DECLARE @TDay VARCHAR(100)
DECLARE @Tid BIGINT
    IF CHARINDEX(',',@str) > 0
BEGIN
        SET  @TDay = SUBSTRING(@str,0,CHARINDEX(',',@str))
SET @Tid=CAST(@TDay AS bigint)
END
    ELSE
        BEGIN
        SET  @TDay = @str
SET @Tid=0
        SET @str = ''
        END
  INSERT INTO  #Temp VALUES (@TDay,@Tid)
 SET @str = REPLACE(@str,@TDay + ',' , '')
 END




BELOW IS WITH LIVE EXAMPLE

USE [STRATEOwnership]
GO
/****** Object:  StoredProcedure [dbo].[sp_getBeneficialHolderDetails]    Script Date: 2017/10/10 11:30:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_getBeneficialHolderDetails]
@IDs varchar(500)
AS
BEGIN
SET NOCOUNT ON;

/* Get all IDS after splitting*/
DECLARE @str VARCHAR(500) = @IDs
CREATE TABLE #Temp (tDay VARCHAR(100),Tid BigInt)
WHILE LEN(@str) > 0
BEGIN
    DECLARE @TDay VARCHAR(100)
DECLARE @FundHolderDetailID BIGINT
    IF CHARINDEX(',',@str) > 0
BEGIN
        SET  @TDay = SUBSTRING(@str,0,CHARINDEX(',',@str))
SET @FundHolderDetailID=CAST(@TDay AS bigint)
END
    ELSE
        BEGIN
        SET  @TDay = @str
SET @FundHolderDetailID=0
        SET @str = ''
        END
  INSERT INTO  #Temp VALUES (@TDay,@FundHolderDetailID)
 SET @str = REPLACE(@str,@TDay + ',' , '')
 END


SELECT [t0].[Holdings], [t0].[Unique Identifier] AS [UniqueIdentifier], [t1].[Client Name] AS [ClientName], [t1].[ClientName1], [t1].[Address1], [t1].[Address2]
FROM [BeneficialShareHolder] AS [t0], [CompanyDetails] AS [t1]
WHERE ([t0].[FundHolderDetailID] in (SELECT Tid FROM #Temp)) AND ([t0].[CompanyDetailsID] = ([t1].[CompanyDetailsID]))


drop table #Temp

END