Number2 Blog - myITforum

BACKGROUND

Pass multiple items into a single parameter?

With SMS Web reports, there's a lot of power in being able to pass parameters to your report query and have it substitute those values into the SQL code.  It really saves you a lot of time not having to write your own front end for your SQL queries.  But the way the SMS Web Report process was designed seems to be focused around passing parameters that are single strings, not multiple lines of text or delimited lists.  If you pass in a really long string, something in the web report process will truncate it down to 255 characters...which is only good for about 15-25 items  To solve that, you can script something yourself using vbscript which bulk-imports a list into a temp table so you can manipulate them from there...which I've done...and is a fine solution when you have a long (> 100 item) list to work with and when you have physical access to the disk on the SQL server.  But if you don't have access like that to the SQL server or you don't want to go that route, then there's another way.  I'll discuss that in this article.

Convert a delimited list to a table?

Subject change: Forget SMS Web reports for a second.  People often want to know how to convert a delimited list into a table or rows like:

'computer1,computer2,computer3'

    becomes

computer1
computer2
computer3

There are a couple of ways to do this, but the best way I can think of is to create a table valued function that acts kinda like the JavaScript Split function.  

This article will address both the single parameter issue and the delimited list issue.  We will learn how to pass a list of things to our web reports and convert that list to a table so we can then act on it.

THE SOLUTION (CODE CAN BE FOUND HERE, BUT PLEASE READ THE ARTICLE)

In order to pass multiple items into SMS Web reports we have the following two main obstacles to overcome:

1. When you pass in a delimited list, there's no out-of-the-box way to convert it to a table and join it to other tables/views.
2. By default, only the first 255 characters of a parameter's value make it through to the report query

So to tackle all that, we're going to have to do the following:

1. Create a function to convert a delimited list to a table
2. Find what part of the web report process that's limiting us to 255 characters and expand the limit.
3. Create a web report to test the above 2 items

Join a delimited list to a table/view
First, let's create our own user defined function...in this case, a table-valued function (TVF), to tackle the list-to-rows (or list-to-column or list-to-table--depending on how you like to say it) problem.  This function needs to do the following:

1. Receive a delimited list and a delimiter
2. Parse through the list and pick out each item that's delimited, and exclude the delimiter from selection.
3. (optional) trim any spaces from the right and left sides of the item
4. Output each item as a row of a table

A table-valued function is a function that we will be adding to our database which you can select from as if it were a table or a view.  But the difference is that you can pass it some parameters which it can use to filter the results that come back.  Think of it as a way to make a parameterized view.  

A table valued function definition looks kinda like this:

create function <name>
(
    <parameters it will take go here>
)
RETURNS @<table variable name>  TABLE
(
    <definition of your output columns goes here>
)
AS
BEGIN
   <do something>
   <insert values into @tablevariable defined above before exiting>
   RETURN
END

So, you take some parameters, do something, and insert the values into a table variable that will be returned back to whatever called it and can then be selected from like a table/view.

So let's put together what our split function should look like:

First, the function name and parameters:
                         
CREATE FUNCTION [dbo].[udf_Split]
(
    @List      VARCHAR(MAX),
    @Delimiter VARCHAR(255) = ','
)  

Let's call it udf_Split.  It's a user defined function, that's going to act like the JavaScript Split function and return rows from a delimited list (well, the Split function actually returns an array you can enumerate through, but it's the same concept so I think it's intuitive enough...but hey, call it whatever you want).

It takes 2 parameters, the delimited list, and the delimiter that's separating the items in that list.  I'm using SQL 2005 right now so I'm using VARCHAR(MAX) for the LIST parameter size, but for SQL 2000 or lower you'll have to use VARCHAR(2000) or something.  

As for the delimiter, it's typically a single character like a comma or a colon or semicolon or space or something but I'm saying varchar(255) so the "delimiter" could be a series of characters if you wanted, not just one...like a carriagereturn-linefeed or some XML tags or something--the function should still work if you do that.  For our purposes we'll pretty much stick to 1 or 2 characters, but I want this to be as useful as possible for purposes outside just web reports.

The output table definition

   RETURNS @RetVal TABLE
(
    ID    INT          IDENTITY,
    Item  VARCHAR(128)
)

This tells SQL that the output for this function is going to be a table that we'll call @RetVal (you could call it anything starting with an @ sign).  The output table has 2 columns, an identity column called ID which will just hold an automatically generated sequence of integers like row numbers (the IDENTITY keyword determines that column will be an auto-generated sequence), and an ITEM column which will hold the extracted items from the delimited list.  Now personally, I don't know if the ID column is that necessary, but it gives the table a unique field.  The SQL purists will probably kick you in the junk if you don't have an ID column so I'm putting it there...I like my junk un-kicked.

The opening

AS  
BEGIN    

This just signifies that we're starting the block of code that will make up the body of the function.

Loop through the list looking for delimiters

   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

This WHILE loop code does most of the magic.  It says the following in English:
1) Loop as long as there is a delimiter found in the list
2) Insert into the output stream the result of the following:
3) Find the postition of the first delimiter and subtract 1 (this will be the position of the first item's last character)
4) Starting at the beginning of the string, select every character up to that position
5) Trim any white space off the right side of the string
6) Trim any white space off the left side of the string
7) Alias the results as the ITEM column...this will now be inserted into @RetVal (the output table)
8) Now, delete the ITEM we just selected out of the master list by doing the following:
9) Find the position of the first delimiter in the list again and add the length of the delimiter.  This will be the new starting point for the list.
10) Select the rest of the LIST starting after said first delimiter and overwrite the master @List with this new value.  It will now be 1 item shorter.
11) repeat until the list has been so shrunk that there are no more delimiters to find

Put any remaining item into the output table

   INSERT INTO @RetVal (Item)
   SELECT VALUE = LTRIM(RTRIM(@List))

This code is necessary because it's possible in the previous code that we searched for a delimiter and found none but one last item still exists in the @List.  So let's take whatever's left, trim it of surrounding whitespace, and insert it into the output table.

Finish the function

   RETURN
END

Our function needs a RETURN to signify we're done processing and it can return the output table back to whatever called it.
And lastly an END statement to signify the end of the function definition.

Now let's see all that code together

SNIPPET #1 - udf_Split - FUNCTION CODE

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

Now, if you run that code from SQL Server Management Studio (SSMS), it will create the function in your SMS database.  

Grant rights needed to run this from an SMS web report

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.

Let's test the new function

Run the following from SQL Management Studio to make sure the function does what we're expecting.

select * from dbo.udf_Splt('one,two,three,four',',')

OUTPUT

ID  Item
1   one
2   two
3   three
4   four


OK, item 1 is done.  We've created a function to convert a delimited list into a table so we can work with it. Now we need to find out what's limiting our web reports to 255 characters.

WEB REPORT LIMITATIONS

Like I mentioned in the introduction, when you pass a long string to a web report, only 255 characters of the string make it to your SQL code.  I'm not exactly sure why 255 characters, but I'd personally like to find a way around that.

DISCLAIMER:  OK, like any of my posts where we diverge from normally supported operations, I must state that the following is neither endorsed nor suppored by Microsoft.  This is just something I've come up with on my own.  Please make sure you're stuff is backed up, and you know what you're doing.  Also, test, test, test that this is going to work for you before trying it in production.  I'm not recommending that anyone actually do this, nor do I fully understand what all of the ramifications may be, but I've investigated it and the following has worked well for me.

Now, that being said, unsupported doesn't mean it shouldn't be done or that it's impossible to do.  It just requires more careful consideration.

Each new web report gets it's own stored procedure

When you create AND RUN a web report, a new stored procedure gets created that stores the SQL code you entered when defining the report. It would appear that the stored procedure gets named:

dbo.wrspXXXYYYYY

Where XXX is the three letter site code of the current DB and YYYYY is the report number.  
To see these, open SQL Management Studio and navigate to:

DATABASES
+ SMS_XXX
  + Programmability
    + Stored Procedures
      + dbo.wrspXXXYYYY

When you RIGHT CLICK on the stored procedure and click MODIFY, it will bring up the code of the report.  Here's an example:

ALTER PROCEDURE [webreport_approle].[wrspXXX00619]
                           @List VARCHAR(255),
                      @Delimiter VARCHAR(255),
               @__timezoneoffset INT  = 0
AS
   BEGIN
      <report SQL code was here>
   END    

Well, you can see right there that the parameters are at most 255 characters.  If you change these to VARCHAR(2000) or something larger, it will allow you to pass in a larger string.
WARNING!  Any changes here will be overwritten then next time somebody alters the report in any way.  So this isn't what we want to change.

I want to change whatever it is that creates this stored procedure in the first place.  I tracked that down to the stored procedure dbo.sp_CreateReportProc.

DATABASES
+ SMS_XXX
  + Programmability
    + Stored Procedures
      + dbo.sp_CreateReportProc

When you RIGHT CLICK on this stored procedure and click MODIFY, it will bring up it's code.  MAYBE IT'S A GOOD IDEA TO BACK THIS UP BEFORE MODIFYING IT, EH?

SNIPPET #2 - sp_CreateReportProc - UNMODIFIED DEFAULT CODE (for 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(255)'
   ELSE
     SET @arglist = @arglist + ', @' + @varname + ' varchar(255)'
   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  

Towards the bottom of the code there are a couple of "varchar(255)" references.  Reading the SQL code it would seem that this is exactly what we're looking for.  This code enumerates the report parameters (prompts) that were set up when the report was created and hard-codes them as VARCHAR(255)...if you change them to VARCHAR(max) for SQL 2005 or VARCHAR(2000) or something for SQL 2000, then we can enter a lot more data.

SNIPPET #3 - sp_CreateReportProc - MODIFIED CODE TO REMOVE 255 CHARACTER LIMITATION
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, if we run the above code it will alter the procedure that makes the web report's stored procedures to support longer strings.

NOTE - BROWSER URL LIMITATION:  Sure, VARCHAR(MAX) with SQL 2005 or higher will leave no limitation, but just so you know, the way web reports work, the URL of the report results contains all of the parameters that you've entered when you first open a report that has parameters.  This is nice because you can see them and you can modify them and you can give people links directly to the results page you want them to have.  The problem with this is that Internet Explorer limits you to 2083 characters (see this KB article for more information http://support.microsoft.com/kb/q208427/).  So it doesn't matter if your report accepts a super long string, the string will get truncated at 2083 and you may receive errors or other wierd behavior.  


CREATE TEST REPORT

OK, what do we have right now...we have a function that converts a delimited string into rows in a table, and we have the 255 character limitation removed.  Let's create a report that will make sure this is true and make sure we haven't broken the whole system.

This article isn't intended to be a primer on Web Reports so I won't go into too much detail here on creating web reports.

Create a web report and give it the following SQL code:

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

This 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


TEST THE REPORT

Now, we just need to test the report

Run the report you just created and pass in the following parameters:
LIST:      1234567890,1234567890,1234567890,1234567890,1234567890,
DELIMITER: ,    <-- that's a comma.  It should already be there

When you click DISPLAY you should see the list you passed in as a bunch of rows!  Woo hoo!
Now try entering a really big list of items.  Make sure it's more than 255 characters.  In practice, I've been able to enter about 150 items that are 10 characters in length, plus the delimiter.  Your mileage may vary.

SUMMARY AND NEXT STEPS  (THE CODE FOR THIS ARTICLE IS HERE)

1. We've learned that SMS Web reports have a hard-coded parameter length limit of 255 characters.
2. We've learned how to create a function to mimic the JavaScript Split function to convert a list into rows
   
3. We've learned the limitation to the web reports comes in through the sp_CreateReportProc stored procedure and how to fix it.
4. We've learned about the 2083 character URL limit and that web reports use the URL to pass parameters to the results page.
5. We've created a test report to make sure the udf_Split function works

From here you can create your own reports that will take a list of computer names or user names and join them to other views.

Someone emails you a list of computers and says, "what's the patch status of these 100 machines?"
Paste them into your report and quickly tell them.

HINT:  Try joining the results of the dbo.udf_Split function using something like this:

SELECT Item as ComputerName,
       sys.User_Name0 as UserName,
       sys.Operating_System_Name_And0 as OS
  FROM dbo.udf_Split(@List,@Delimiter) AS ComputerList
       INNER JOIN dbo.v_R_System as sys
          ON sys.Netbios_Name0 = ComputerList.Item
         AND sys.Decommissioned0 = 0
         AND sys.Obsolete0 = 0
         AND sys.Client0 = 1

 

Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog