Code for a View / Function comination to gernerate random numbers in tSQL

The RAND function does not behave as you would expect in SQL Server. If it is included in a select statement then it will generate a single number and populate every row with that value.

Here is a bit of a complex way of getting around this problem. Wrapping the RAND function in a view and calling via a UDF.

Modify as required but remember be careful of resource usage calling functions in select statement

CREATE VIEW vw_RandNumber_tempDEVOnly
AS

--gerard 28/10 this is a temporary setup to generate random data to allow me to develop a report in a system where we haven't put the required grouping data
SELECT RAND() as RandNumber

ALTER FUNCTION fRandNumber_tempDEVOnly(@Min int, @Max int)
RETURNS float
AS
--gerard 28/10 this is a temporary setup to generate random data to allow me to develop a report in a system where we haven't put the required grouping data

 BEGIN
 RETURN ROUND(@Min + (select RandNumber from vw_RandNumber_tempDEVOnly) * (@Max-@Min),0)
 END


--call like this
 SELECT dbo.fRandNumber_tempDEVOnly(1,3)

SSRS subreports inserting pagebreaks.

It can be frustrating when a subreport (or any report item) inserts pagebreaks a and you have to go through and get the KeepTogether and KeepWithGroup properties correct AND correct at the right level.

KeepWithGroup in the parent report is the key property and the key level is at the static row group the sub report is in for the Parent Report.

If you are having pagebreaks inserted a rule of thumb is

• Make sure KeepTogether is set to False everywhere it is not explicitly required
• KeepWithGroup = After for the report item that is creating the pagebreaks (in this case the sub-report)

SSRS - Add a label listing items selected from a multi select parameter

The following code in ds_Vendor_Type_SelectedList returns a string that can be used in the report heading to describe the values selected in a multi select parameter.

A simple alternative might be to use the VB code
=Join(Parameters!ParameterName.Label, ",")
But in large parameter selections this does not indicate to the user if ALL the parameters have been selected.

If all parameters are selected then the following structure returns 'ALL' or alternatively, if a subset is selected then it lists the actual items. This is a more intuitive and user friendly approach.

The second data set called ds_Vendor_Type_SelectedList is populated based on the user selection of the parameter (@VendorTypes parameter variable)



Parameter Name = @VendorTypes

List populated by following Dataset
ds_VendorType_List

SELECT     VENDTYP_CODE,VENDTYP_DESC
FROM APL_VENDTYP
WHERE INACTIVE = 'N'
ORDER BY VENDTYP_DESC


ds_Vendor_Type_SelectedList

DECLARE @CountSelected AS INT
DECLARE @CountAvailable AS INT
DECLARE @ReturnString AS VARCHAR(MAX)

SET @CountAvailable =
    (SELECT COUNT(VENDTYP_CODE)
    FROM APL_VENDTYP
    WHERE INACTIVE = 'N')
SET @CountSelected =
    (SELECT COUNT(VENDTYP_CODE)
    FROM APL_VENDTYP
    WHERE VENDTYP_CODE  IN (@VendorTypes))

IF @CountAvailable = @CountSelected
    BEGIN
        SET @ReturnString = 'All Vendors Selected'
    END
ELSE
    BEGIN
        SET @ReturnString = 'Vendors Selected : ' +
        (STUFF(( SELECT ', ' + VENDTYP_DESC
        FROM APL_VENDTYP WHERE VENDTYP_CODE IN (@VendorTypes)
        FOR XML PATH('')), 1, 2, ''))
    END
   
SELECT  @ReturnString AS VendorsSelected  

Handy code snippits for exploring the ssrs database

/*
    Ran instances oof reports with the parameters
*/
SELECT    x.TimeStart
        ,c.Name
        ,x.USERNAME
        ,x.Parameters
        ,c.Type
        ,x.TimeDataRetrieval
        ,x.TimeProcessing
        ,x.TimeRendering
        ,x.ByteCount
        ,x.[RowCount]
        ,c.Path
       
               ,'XX' as 'Catelog_Fields_Start_Here'
               ,c.*
               ,'XX' as 'ExecutionLog_Fields_Start_Here'
               ,x.*
          FROM Catalog c
               INNER JOIN
               ExecutionLog x
                 ON c.ItemID = x.ReportID
         WHERE Type = 2 AND x.USERNAME like '%gerard%'
         order by x.TimeStart DESC
/*
    code to get xml of report from content field
*/
        
--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
    SELECT
    ItemID,Name,[Type]
        ,CASE Type
        WHEN 2 THEN 'Report'
        WHEN 5 THEN 'Data Source'
        WHEN 7 THEN 'Report Part'
        WHEN 8 THEN 'Shared Dataset'
        ELSE 'Other'
    END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
    FROM ReportServer.dbo.Catalog
    WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
    SELECT
    ItemID,Name,[Type],TypeDescription
    ,CASE
        WHEN LEFT(Content,3) = 0xEFBBBF
        THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
    ELSE
            Content
        END AS Content
    FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
    SELECT
    ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
    FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
    ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

-- dsMain
SELECT
ReportID,
Name AS ReportName,
InstanceName,
COALESCE(C.Path, 'Unknown') AS ItemPath,
UserName,
ExecutionId,
CASE(RequestType)
  WHEN 0 THEN 'Interactive'
  WHEN 1 THEN 'Subscription'
  WHEN 2 THEN 'Refresh Cache'
  ELSE 'Unknown'
END AS RequestType, -- SubscriptionId,
Format, Parameters,
CASE(ReportAction) WHEN 1 THEN 'Render'
  WHEN 2 THEN 'BookmarkNavigation'
  WHEN 3 THEN 'DocumentMapNavigation'
  WHEN 4 THEN 'DrillThrough'
  WHEN 5 THEN 'FindString'
  WHEN 6 THEN 'GetDocumentMap'
  WHEN 7 THEN 'Toggle'
  WHEN 8 THEN 'Sort'
  WHEN 9 THEN 'Execute'
  ELSE 'Unknown'
END AS ItemAction,
TimeStart,
TimeEnd,
TimeDataRetrieval,
TimeProcessing,
TimeRendering,
CASE(Source)
  WHEN 1 THEN 'Live'
  WHEN 2 THEN 'Cache'
  WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History'
  WHEN 5 THEN 'AdHoc'
  WHEN 6 THEN 'Session'
  WHEN 7 THEN 'Rdce'
  ELSE 'Unknown'
END AS Source,
CASE (Status)

  WHEN 'rsHttpRuntimeClientDisconnectionError' THEN 'HttpRuntimeClientDisconnectionError'
  WHEN 'rsReportServerDatabaseError' THEN 'ReportServerDatabaseError'
  WHEN 'rsProcessingAborted' THEN 'Processing Aborted'
  WHEN 'rsSuccess' THEN 'Success'
  WHEN 'rrRenderingError' THEN 'Rendering Error'
  ELSE 'Unknown'
END AS Status,
ByteCount,
[RowCount],
AdditionalInfo
FROM ExecutionLogStorage EL WITH(NOLOCK)
LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
WHERE ReportID = @ReportList
;

--dsSSRSReportList
Select    ItemID
        ,[Name]  
From    [Catalog] c
Where  [Type] = 2
AND [Hidden] <> 1
AND SubString([Path],1,Len([Path])-(CharIndex('/',Reverse([Path]))-1))  = @FolderList
ORDER BY c.[Name]

--dsSSRSFolderList
--Returns a list of the folder structure on SSRS Server
SELECT DISTINCT SubString([Path],1,Len([Path])-(CharIndex('/',Reverse([Path]))-1)) As [Path] From    [Catalog] c



create VIEW [dbo].[vw_REPORT_JWS_SSRS_ReportUsage]
AS
       SELECT    ReportName,[Path]
             ,Runs
             , DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 AS DaysHistoryPresent
             ,CASE
                WHEN  DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 = 0 OR Runs = 0 THEN 0
                ELSE  Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)
             END AS ApproxRunsPerDay
             , Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)*30  AS ApproxRunsPerMonth
       FROM
       (
          SELECT
             c.Name AS ReportName
             ,c.Path
             ,Count(1) AS Runs
             ,MIN(el.TimeStart) AS FirstDay
             ,MAX(el.TimeStart) AS LastDay
          FROM ExecutionLogStorage EL WITH(NOLOCK)
             LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
          WHERE   el.Source <> 6
          AND [Name] NOT IN ('Global', 'Default','dsRptFmt','dsRptFmt_Internal','Dummy')
          GROUP BY c.Name,c.Path
       ) AS a
       ORDER BY
       CASE
          WHEN DATEDIFF(dd, FirstDay,LastDay)+1 = 0.000 OR Runs = 0 THEN 0.000
          ELSE (Runs/ DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1)
       END DESC
GO
        

SSRS get the run history and parameters used for individual report

USE [ReportServer]
/*
useful code for getting the run history and parameters used of a report
*/
DECLARE @ReportName AS VARCHAR(MAX)
SET @ReportName = '%Report1%'--report name hers surrounded by wildcards

SELECT * FROM
(
    SELECT
    InstanceName,
    COALESCE(C.Path, 'Unknown') AS ItemPath,
    UserName,
    ExecutionId,
    CASE(RequestType)
      WHEN 0 THEN 'Interactive'
      WHEN 1 THEN 'Subscription'
      WHEN 2 THEN 'Refresh Cache'
      ELSE 'Unknown'
    END AS RequestType, -- SubscriptionId,
    Format, Parameters,
    CASE(ReportAction) WHEN 1 THEN 'Render'
      WHEN 2 THEN 'BookmarkNavigation'
      WHEN 3 THEN 'DocumentMapNavigation'
      WHEN 4 THEN 'DrillThrough'
      WHEN 5 THEN 'FindString'
      WHEN 6 THEN 'GetDocumentMap'
      WHEN 7 THEN 'Toggle'
      WHEN 8 THEN 'Sort'
      WHEN 9 THEN 'Execute'
      ELSE 'Unknown'
    END AS ItemAction,
    TimeStart,
    TimeEnd,
    TimeDataRetrieval,
    TimeProcessing,
    TimeRendering,
    CASE(Source)
      WHEN 1 THEN 'Live'
      WHEN 2 THEN 'Cache'
      WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History'
      WHEN 5 THEN 'AdHoc'
      WHEN 6 THEN 'Session'
      WHEN 7 THEN 'Rdce'
      ELSE 'Unknown'
    END AS Source,
    Status,
    ByteCount,
    [RowCount],
    AdditionalInfo
    FROM ExecutionLogStorage EL WITH(NOLOCK)
    LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
)AS a
WHERE ItemPath like @ReportName   

Search and Locate report items the SSRS database (ie which reports did I use the SQL function i am modifying in etc)

Following code will list report objects in a Reporting Server Database. It can be modified to serarch for specific items in specific reports (ie which reports contain reference to a certain data set or paramater list etc)



--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM ReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
  FROM ItemContentBinaries
  where Name like '%%' --add filter here
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
     ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML

--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

SSRS Troubleshooting notes for Data connection Errors

Datasets unable to refresh fields

  The size necessary to buffer the XML content exceeded the buffer quota

  This often occurs when building a report based on a previous report
  Often it is because you are referencing fields that are not in the existing data scope,
  You are probably referencing a dataset or fields that have not been included in the report yet

  An item with the same key has already been added.

      Duplicate field names in your SQL

  Must declare the scalar variable "xx". (when this Parameter already exists)

      1) Add the Parameters Manually in the Parameters Tab of the Dataset Properties
    
      2) Report xml is scrambled - Multiple reasons can cause this but I had a big problem with this occurring when I was converting crystal reports to SSRS and using an online conversion tool to do the basics (and set up all the positioning and formatting as per the original which some of these tools are very good at)

      I have never put the time and effort into working out what the problem was. Instead I have opened a fresh report and quickly rebuilt it which has always worked.

       Open a blank report and rebuild the report across the tip
        Copy parameters from the xml and add
        Copy and Paste all the actual graphical interface of the report to the new report
        Build the datasets manually in 
     

SSRS Displaying column headers

It is easy to forget what is required to display column headers

There are actually 3 settings need to be set      



    Tablix Properties  
        Repeat Header Row on each page needs to be checked
    Group -> Select Advanced _> Select the following 2 properties  
        RepeatOnNewPage to True
        KeepwithGroup to After


The same applies for column headers

Using HTML markup to format text in SSRS

You have a number of options for formatting text in SSRS. Here is a good article on formatting options for text boxes. The step by step HTML section is below. Thanks SQLchick,

http://www.sqlchick.com/entries/2010/10/31/using-different-formats-within-a-single-textbox-in-ssrs.html

HTML Tags

This option delivers a bit more flexibility, particularly if you have a complex expression in your textbox.  Using HTML tags will work if you have a complex expression (i.e., shown in the design view).
First, you do need to define Placeholder Properties on the Expression:

Within the Placeholder Properties, change the default Markup type to be “HTML – Interpret HTML tags as styles.”  If you forget to change this radio button, then the html tags will be rendered as literal text.

Then within your expression, insert the HTML tags as needed.

Only a subset of HTML tags are supported within SSRS, such as bold, italics, and underline.  The MSDN page on Formatting Text and Importing HTML specifies the valid HTML tags you may use within an SSRS textbox.  If you use an HTML tag that isn’t supported in SSRS, it will be ignored.
As a sidenote, using HTML tags within a simple expression will work as well:

However, in a real situation, I would reserve using HTML tags to situations when I have a complex expression.  With a simple expression, I’d opt to keep the formatting options simpler.

Passing Multi-Value Parameters In Reporting Services

Here is a quick and efficient way to pass and use multi value parameters in SSRS and your stored procedures or SQL.

Concept

•  Convert the parameter array to CSV
•  In the SSRS report, on the parameters tab of the query definition, set the parameter value to:
• =join(Parameters!YourParamName.Value,",")
• Create a Table Valued function to convert the CSV string to a table that can be addressed as per normal (See function below - there are many variation on the web)
• Join or or use this function as per a normal sub SELECT statement (See example sp below

You can also use a simple text box to pass a  comma separated string to SQL server directly. The user simply enters a comma separated list of values. This is useful where the user may need to select multiple values from a list that is too large to be displayed as a multi select drop-down (ie a company might have many thousands of customers which is too many to display in a drop down - but entering half a dozen client codes directly into a text box is a viable solution)

Gerard

User defined function for numeric strings

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
======================================================================================================================================
Author:            Gerard
Create date:   
Description:    Returns a recordset from SSRS multi select parameter that has been passed into a stored procedure
                (turns a comma seperated string into a records set)
Requirements:    Paramater variable must be formed up into a comma deliminated string (or some other delimiter if the data
                already contains commas)
                Parameters can be passed in directly from SSRS often but some data types need to be massaged in SSRS
                    This is achieved in the SSRS dataset that is calling the sp by using the VB JOIN function
                        DS calling SP -> Properties -> Parameters
                        Add Parameter that is multi select -> add JOIN script to concat selected values with commas (or other char if data contains commas)
                        EG Param=-ResponsiblePartner   Expr=JOIN(Parameters!ResponsiblePartner.Value,",")
               
               
               
                Example of usage in SP
                WHERE EMPLOYEE_CODE IN (SELECT * FROM DelimitedSplit8K(@RespPartners, ','))
                INNER JOIN (SELECT * FROM DelimitedSplit8K(@RespPartners, ',')) AS t ON t.Item = s.CUST_CODE


NOTE:                a useful bit of code to create a comma delimited string from a recordset
                    is below (useful place to leave it)
                    SELECT @RespPartners = COALESCE(@RespPartners + ',','') +  EMPLOYEE_CODE
                    FROM _JWS_Responsible_Partners
               
Modifications:    ALL MODIFICATIONS MUST BE DETAILED BELOW SHOWING DATE

Modified By:    Gerard   
Date:           
Modification:    modified DelimitedSplit8K to return a Integer recordset where multi select field is numeric

======================================================================================================================================



*/
ALTER FUNCTION [dbo].[DelimitedSplit8K_Int]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = CAST(SUBSTRING(@pString, l.N1, l.L1) AS INT)
   FROM cteLen l
   WHERE ISNUMERIC(SUBSTRING(@pString, l.N1, l.L1))=1
;

User defined functionfor text strings

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
======================================================================================================================================
Author:            Unknown
Create date:   
Description:    Returns a recordset from SSRS multi select parameter that has been passed into a stored procedure
                (turns a comma seperated string into a records set)
Requirements:    Paramater variable must be formed up into a comma deliminated string (or some other delimiter if the data
                already contains commas)
                Parameters can be passed in directly from SSRS often but some data types need to be massaged in SSRS
                    This is achieved in the SSRS dataset that is calling the sp by using the VB JOIN function
                        DS calling SP -> Properties -> Parameters
                        Add Parameter that is multi select -> add JOIN script to concat selected values with commas (or other char if data contains commas)
                        EG Param=-ResponsiblePartner   Expr=JOIN(Parameters!ResponsiblePartner.Value,",")
               
               
               
                Example of usage in SP
                WHERE EMPLOYEE_CODE IN (SELECT * FROM DelimitedSplit8K(@RespPartners, ','))
                INNER JOIN (SELECT * FROM DelimitedSplit8K(@RespPartners, ',')) AS t ON t.Item = s.CUST_CODE


NOTE:                a useful bit of code to create a comma delimited string from a recordset
                    is below (useful place to leave it)
                    SELECT @RespPartners = COALESCE(@RespPartners + ',','') +  EMPLOYEE_CODE
                    FROM _JWS_Responsible_Partners
               
Modifications:    ALL MODIFICATIONS MUST BE DETAILED BELOW SHOWING DATE

Modified By:    Gerard       
Date:            28/08/15
Modification:    Space in comma seperated string prevents match ie 'an1, dhp1' returns an1 while 'an1,dhp1' returns an1 and dhp1


Modified By:           
Date:           
Modification:   
======================================================================================================================================



*/
ALTER FUNCTION [dbo].[DelimitedSplit8K]
    (@pString VARCHAR(8000), @pDelimiter CHAR(1))


RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
   --SET @pString =REPLACE(@pString, ' ', '')

--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
    WITH
        E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
        )--10E+1 or 10 rows
        ,E2(N) AS (SELECT 1 FROM E1 a, E1 b) --10E+2 or 100 rows
        ,E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
        ,cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
            SELECT TOP (ISNULL(DATALENGTH(REPLACE(@pString, ' ', '')),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
        )
        ,cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
            SELECT 1 UNION ALL
            SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(REPLACE(@pString, ' ', ''),t.N,1) = @pDelimiter
        )
        ,cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
            SELECT s.N1,
            ISNULL(NULLIF(CHARINDEX(@pDelimiter,REPLACE(@pString, ' ', ''),s.N1),0)-s.N1,8000)
            FROM cteStart s
        )
       
        SELECT ItemNumber,Item FROM
        (
            --Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
            SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
                Item= SUBSTRING(REPLACE(@pString, ' ', ''), l.N1, l.L1)
            FROM cteLen l
        ) as a
        WHERE REPLACE(Item, ' ', '') <> ''
       
;
;

Example sp
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
======================================================================================================================================
TestOnly
======================================================================================================================================
Example Call
exec udspr_REPORT_Gerard_Test2 '1010,1011,2010,3013,4012'

*/
CREATE PROC [dbo].[sp_Gerard_Test2]
(


    @CSVIn VARCHAR(8000) = NULL
   
)
AS
BEGIN
SELECT * FROM DelimitedSplit8K(@CSVIn, ',') AS wt

/*
Example of use in Inner Join
INNER JOIN (SELECT Item FROM DelimitedSplit8K(@PracticeGroup, ',')) AS wt ON (SomeTable.SomeField = wt.Item)
Example of most common use - IN
WHERE x.x IN((SELECT Item FROM DelimitedSplit8K(@PracticeGroup, ',')
*/

END