ssrs 2008 stored procedure not working

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  April 2, 2016 at 3:16 pm

  #315050

  In a new SSRS 2008 report, I am going to change the inline sql to a stored

  procedure since that is a requiement for the project I am working on. The sql works fine

  within the ssrs 2008 report, but has a problem in the stored procedure.

  The error message that is displayed is the following:

  'Query execution failed for dataset Msg 8114, Level 16, State 1 Procedure spRec,

  line 0 Error converting data type varchar to int.'

  The stored procedure works if I select only 1 report. However if I select 2 or more reports that is when the above error occurs.

  The ssrs 2008 report has 18 embedded tablixes within the main tablix. The

  embedded tablixes wiithin the ssrs main tablix will display selected reports based upon a

  report parameter that a user. The embedded reports will be displayed based upon the report

  number. In addition, I only want the data for the selected reports to be returned from

  the sql. I want this to occur since the data from the runs will be more than is

  needed for each uniuqe run. Note: The data returned will be all in the same format. There

  will be a column in each data row showing what report number the

  data should be selected for.

  The report parameter is setup as allow multiple values and data type = text.

  The dataset to pass data to the report parameter looks like:

  SELECT 'Locator' AS rptName, 1 AS rptValue

  UNION

  SELECT 'letter',2

  UNION

  SELECT 'Wallet ', 3

  UNION

  SELECT 'Cum Stu', 4

  UNION

  SELECT 'Attend', 5

  UNION

  SELECT 'Test',6

  The sql before each section looks like:

  IF 1 in (@report)

  IF 2 in (@report)

  I have tried changing the properties of the report parameter to integer and the

  if statements listed above but it has not worked.

  Thus can you tell me what I can do to solve my problem?

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  April 2, 2016 at 5:36 pm

  #1869345

  There is a subtle difference between the way a query embedded in the report is run compared to calling a stored procedure, and the result is that your parameter is now being passed to the stored procedure as a string of comma separated values, like this:

  "2,5,6"

  So, your IF statements now need to test for the required value within the string.

  The simplest way (IMHO) is to just use charindex to look for your value with commas round it. The slight tweak you need to make is to add commas round the whole string (parameter list) first.

  So, something like this

  DECLARE @localList varchar(512) = ',' + @report + ',';

  -- check for report "1"

  IF CHARINDEX(',1,', @localList)>0

  BEGIN

  ...

  END

  -- check for report "2"

  IF CHARINDEX(',2,', @localList)>0

  BEGIN

  ...

  END

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]