0

Below is code that I built from an example I found online, I can't find the link, but the code is referenced in the answers on this stack overflow question: Passing multiple values for a single parameter in Reporting Services.

Here is the SQL code I am working with right now within my stored procedure, it was a long procedure so I summed it down to just the section I am working on, and added the DECLARE and SET for @EMPLOYEES, which are passed as a parameter from SSRS to make the code snippet run. 

DECLARE @EMPLOYEES varchar(8000)

-- EMPLOYEES is a comma separated list of EMPLOYEE IDS 
-- FROM SSRS Report Parameters. Each ID is 12 characters
-- And there are 806 Employees to choose from, which 
-- when all are selected, the Comma separated string grows 
-- to 11,193 characters, much longer than 8000
SET @EMPLOYEES = 'EMP000000001,EMP000000002,EMP000000003'

CREATE TABLE #EMPLOYEEIDS
(
   EMPLOYEEID varchar(100) NOT NULL
)

DECLARE @CharIndex AS int
DECLARE @Piece AS varchar(100)

-- FILL THE #EMPLOYEEIDS TABLE WITH THE COMMA SEPARATED EMPLOYEE IDS
SELECT @CharIndex = 1
WHILE @CharIndex > 0 AND LEN(@EMPLOYEES) > 0
BEGIN
   SELECT @CharIndex = CHARINDEX(',', @EMPLOYEES)
   IF @CharIndex > 0
      SELECT @Piece = LEFT(@EMPLOYEES, @CharIndex - 1)
   ELSE
      SELECT @Piece = @EMPLOYEES

   INSERT INTO #EMPLOYEEIDS (EMPLOYEEID) VALUES (@Piece)
   SELECT @EMPLOYEES = RIGHT(@EMPLOYEES, LEN(@EMPLOYEES) - @CharIndex)
END

SELECT * FROM #EMPLOYEEIDS

DROP TABLE #EMPLOYEEIDS

I had 6 sets of multi-values, all of them worked fine, until I found that the reports were missing much of the data for employees, to which I found that the VARCHAR(8000) was overflowed when selecting all the employees on the report parameters (there are over 800 of them). The Report would run, SQL would happily truncate the VARCHAR to 8000 characters, and a quarter of the IDS were not parsed.

So I tried to switch the VARCHAR to a text field, and none of the parsing functions would work when the field is set up as TEXT. I get errors like the following:

Msg 8116, Level 16, State 2, Procedure usp_QualityMonitoring_AllProfiles_SelectWithParameters, Line 89
Argument data type text is invalid for argument 1 of left function.

This is understandable, I know that many functions that work with VARCHAR will not work with TEXT. So, SQL is truncating everything after 8000 characters when I use a VARCHAR, and the procedure won't ever run if I switch it to TEXT.

  • What other options to I have to pass multi-valued parameters from SSRS to a SQL Server stored procedure that can support this many options?
  • OR is there a way to fix the code in the stored procedure to parse through TEXT instead of VARCHAR?

Note: I originally thought the SQL Server running the Stored Proc was 2005, but I have determined that it is not:

SELECT @@VERSION
-- Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Community
  • 1
  • 1
stephenbayer
  • 12,373
  • 15
  • 63
  • 98
  • How about using the new type in SQL2005 > VARCHAR(MAX) – Dimi Takis Aug 23 '12 at 15:10
  • Why aren't you using `VARCHAR(MAX)` instead of `TEXT`?? It supports up to 2 GB (2 billion characters!) of text, and all functions that any `varchar` column supports.... – marc_s Aug 23 '12 at 15:10
  • I tried the VARCHAR(MAX) and it wasn't working, getting an error complaining about "MAX" being an unknown blah blah whatever, so I did a SELECT @@VERSION and I realized I am working with SQL Server 2000. I'll change the question to reflect that – stephenbayer Aug 23 '12 at 15:22
  • You have the cards stacked against you: SSRS 2000 was effectively version 1.0 of the product and SSRS, even in more recent versions, has problems with extremely long lists of options in a multi-value parameter. If it were my report, I would look for ways to change the parameter from having to specify every employee individually: can you select department or IDs in a range? – Jamie F Aug 23 '12 at 15:27
  • SSRS is version 2005, the SQL Server that has the data I'm reporting on is 2000. There is a drill down in the parameters, where Manager can be selected and there are much fewer than the employees, then the parameter select has fewer options. So it is unlikely that they will select all managers, then all the employees. But In my tests, I select all, and I should be able to handle all of them if they do make those selections. So in production reality, this probably won't be a problem, but in the realm of possibility, it is an issue. – stephenbayer Aug 23 '12 at 15:34
  • You could limit the selection of managers to a small number. Have the report display an error textbox if more than 10 managers are selected. – Jamie F Aug 23 '12 at 19:20

0 Answers0