I have Split function and want to store its
output to table varaibale.
DECLARE
@ParsedTable AS TABLE (Item VARCHAR(MAX)) ;
INSERT
@ParsedTable SELECT *
FROM dbo.Udf_split('505,468',',');
SELECT * FROM Employee WHERE userid IN (SELECT * FROM @ParsedTable)
-- =============================================
-- Description: Used for Spliting
the list
-- =============================================
CREATE FUNCTION [dbo].[Udf_split] (@List VARCHAR(max), @Delim CHAR)
returns
@ParsedTable TABLE (Item
VARCHAR(max))
AS
BEGIN
DECLARE
@list1 VARCHAR(max),@Pos INT, @rList VARCHAR(max)
SET @list
= Ltrim(Rtrim(@list)) + @Delim
SET @pos = Charindex(@delim, @list, 1)
WHILE
@pos > 0
BEGIN
SET
@list1 = Ltrim(Rtrim(LEFT(@list, @pos - 1)))
IF
@list1 <> ''
INSERT
INTO @ParsedTable VALUES
(Cast(@list1 AS VARCHAR(max)))
SET
@list = Substring(@list, @pos + 1, Len(@list))
SET
@pos = Charindex(@delim, @list, 1)
END
SELECT
@rlist = COALESCE(@rlist+',', '') + item FROM (SELECT DISTINCT Item FROM
@ParsedTable) t
RETURN
END
No comments:
Post a Comment