Need to convert Table Function to SP so I can update my SSRS Report.

Last Post 28 Jun 2010 06:18 AM by Jack_Kaye. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Jack_Kaye
New Member
New Member

--
21 Jun 2010 09:36 AM
I have a report that I created using SQL Report Builder 2008.  I recently added a function to the SQL code that I was testing to pull information into a table that is then checked within the report.  The function works great when running in SQL Server Management Studio but I discovered that there is no way to reference the function from within the Report Builder.  I'm thinking that if I can conver the Function into a SP, I can add a dataset for the SP that can be referenced by the report?  I'm hoping this is the correct path but I'm open to suggestions.

Here is the function that I need to conver:

ALTER FUNCTION [dbo].[fnDeptSplit] 
   ( 
      @sInputList VARCHAR(8000) -- List of delimited items 
      ,@sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items 
   ) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN 
   DECLARE @sItem VARCHAR(8000), @sStartRange INT, @sEndRange INT 
      WHILE CHARINDEX(@sDelimiter,@sInputList,0) 0 
         BEGIN 
            SELECT 
               @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))), 
               @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) 
               IF LEN(@sItem) > 4 -- The result is going to be a range (3403..3405)
                  BEGIN 
                     set @sStartRange = LEFT(@sItem,4) 
                     set @sEndRange = RIGHT(@sItem,4) 
                     set @sItem = @sStartRange 
                  END 
                  WHILE @sItem < @sEndRange 
                     BEGIN 
                        INSERT INTO @List SELECT @sItem 
                        set @sItem = @sItem + 1 
                     END 
               IF LEN(@sItem) > 0 
                  INSERT INTO @List SELECT @sItem 
               ELSE 
                  INSERT INTO @List SELECT NULL 
            END 
            IF LEN(@sInputList) > 0 
               INSERT INTO @List SELECT @sInputList -- Put the last item in 
            ELSE 
               INSERT INTO @List SELECT Code FROM [NAVLIVE5].[dbo].[Blanchard US$Dimension Value] WHERE [Dimension Code] = 'DEPARTMENT' -- Blank entry is all Departments 
      RETURN
END
Jack_Kaye
New Member
New Member

--
28 Jun 2010 06:18 AM


Acceptable Use Policy
---