Surendra Sharma

Surendra Sharma

Search This Blog

Thursday, June 27, 2013

How to assign function output to table variable

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