-- Author: Number2 (John Nelson) - http://number2blog.com
-- Disclaimer: This code is offered as-is and is not provided for any particular purpose
--             I can't guarantee that it works, nor can I guarantee it won't break something.
--             However, it is to the best of my understanding, pretty good code.

-- Modify it to meet your needs and TEST TEST TEST!


-- UDF_SPLIT FUNCTION TO CONVERT DELIMITED LIST TO TABLE
USE <your SMS db here>
GO

CREATE FUNCTION [dbo].[udf_Split]
(
	@List      VARCHAR(MAX),
	@Delimiter VARCHAR(1) = ','
)  
   RETURNS @RetVal TABLE 
(
	ID    INT          IDENTITY,
	Item  VARCHAR(128)
) 
AS  
BEGIN    
   WHILE (CHARINDEX(@Delimiter,@List)>0)
      BEGIN
         INSERT INTO @RetVal (Item)
         SELECT LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@Delimiter,@List)-1))) AS ITEM

         SET @List = SUBSTRING(@List,CHARINDEX(@Delimiter,@List)+LEN(@Delimiter),LEN(@List))
      END
   INSERT INTO @RetVal (Item)
   SELECT VALUE = LTRIM(RTRIM(@List))
   RETURN
END




-- GRANT SELECT PERMISSIONS TO THE NEW (TABLE) FUNCTION
GRANT SELECT ON dbo.udf_Split TO smsschm_users, webreport_approle

/* NOTE:  We're granting SELECT, not EXECUTE on this function.  
This is because it's a table-valued function that acts like a table to SQL.  
For some reason, they require you to grant SELECT, not EXECUTE. */




-- VERIFY UDF_SPLIT WORKS
select * from dbo.udf_Splt('one,two,three,four',',')




-- REMOVE 255 CHARACTER LIMITATION IN NEW REPORTS 
-- (NOTE: THIS HAS ONLY BEEN TESTED ON SMS 2003 SP3!!!)
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
 ALTER PROCEDURE [dbo].[sp_CreateReportProc] @ReportID int AS 
 BEGIN 
 DECLARE @procname varchar(255) 
 DECLARE @sqlprolog varchar(255) 
 DECLARE @arglist varchar(8000) 
 DECLARE @sqlbody varchar(8000) 
 DECLARE @sqlbody2 varchar(8000) 
 DECLARE @varname varchar(255) 
 SELECT @procname = SecurityKey, @sqlbody=SUBSTRING(SQLQuery,1,8000), 
        @sqlbody2=SUBSTRING(SQLQuery,8001,8000) 
    FROM dbo.Report WHERE ReportID=@ReportID 
 IF @@ROWCOUNT = 0 
 BEGIN 
   RAISERROR('Report %d does not exist in the database.',15,1,@ReportID) 
   RETURN 
 END 
 IF @procname IS NULL 
 BEGIN 
   RAISERROR('Report %d does not have a valid security key.',15,1,@ReportID) 
   RETURN 
 END 
 IF @sqlbody IS NULL 
 BEGIN 
   RAISERROR('Report %d does not have a valid security key.',15,1,@ReportID) 
   RETURN 
 END 
 /* prefix the security key to create our procname */ 
 SET @procname = 'wrsp' + @procname 
 IF OBJECT_ID(@procname) IS NOT NULL 
   RETURN 
 DECLARE repprocs INSENSITIVE CURSOR FOR 
     SELECT VarName FROM dbo.ReportParameters 
        WHERE ReportID = @ReportID 
        ORDER BY Sequence 
 OPEN repprocs 
 FETCH NEXT FROM repprocs INTO @varname 
 WHILE (@@FETCH_STATUS <> -1) 
 BEGIN 
   IF (@arglist IS NULL) 
     SET @arglist = ' @' + @varname + ' varchar(max)' 
   ELSE 
     SET @arglist = @arglist + ', @' + @varname + ' varchar(max)' 
   FETCH NEXT FROM repprocs INTO @varname 
 END 
 CLOSE repprocs 
 DEALLOCATE repprocs 
   IF (@arglist IS NULL) 
     SET @arglist = ' @__timezoneoffset int = 0' 
   ELSE 
     SET @arglist = @arglist + ', @__timezoneoffset int = 0' 
  EXEC('CREATE PROCEDURE ' + @procname + @arglist + ' AS BEGIN ' + @sqlbody + @sqlbody2 + ' END') 
 END



/*

Now, create a web report and give it the following SQL code:


SELECT Item
  FROM dbo.udf_Split(@List,@Delimiter) AS List


That code will allow us to take the passed in list and split it into rows at the delimiter


Create 2 Prompts/Parameters

Name:          List
Prompt Text:   List (delimited list of something)
Default Value: <blank>

Name:          Delimiter
Prompt Text:   Delimiter (comma, pipe, space, etc.)
Default Value: ,
Allow An Empty Value: Checked

*/