June 27, 2011 at 9:06 am
Hello All,
I keep getting this error when I am trying to run a SP with parameters in Reporting Services. The parameter that is giving me an issue is the @year, it works fine when I enter one year value, i.e. 2011 however, when I insert more than one value, i.e. 2010, 2011, it does not run the SP.
I did make sure that the data type is INT in Report Parameters section as well as the T-SQL. I've tried to do conversions but have not been successful.
Can anyone please help me?
My SP looks like this:
@Year int,
@Region varchar(Max),
@Province varchar(50),
@Territory varchar(Max),
@Measure varchar(Max)
AS
BEGIN
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
Select CalendarYear, CalendarMonthName ,
CASE
WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'JAN' THEN 01 WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'FEB' THEN 02
WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'MAR' THEN 03 WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'APR' THEN 04
WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'MAY' THEN 05 WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'JUN' THEN 06
WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'JUL' THEN 06 WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'AUG' THEN 08
WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'SEP' THEN 09 WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'OCT' THEN 10
WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'NOV' THEN 11 WHEN UPPER(LEFT(c.CalendarMonthName,3)) = 'DEC' THEN 12
END AS MonthlyOrder,
CASE
When @Measure = 'NON_FTTN_Voice' Then NON_FTTN_LOAD/NAS
When @Measure = 'FTTN_Groomed' Then FTTN_LOAD/FTTN_NAS
When @Measure = 'DSL' Then DSL_LOAD/DSL_NAS
When @Measure = 'Non_Groomed_DSL' Then FTTN_NON_GROOMED_DSL_LOAD/FTTN_NON_GROOMED_DSL_NAS
When @Measure = 'Groomed_DSL' Then FTTN_DSL_LOAD/FTTN_DSL_NAS
END As Service,
Region, Province, Territory
From dbo.Table_1 a
JOIN dbo.Table_2 b
ON a.pk_region = b.pk_Region
JOIN dbo.Table_3 c
ON a.pk_time = c.pk_time
Where CalendarYear IN (@Year)
and Region IN (@Region)
and Province IN (@Province)
and Territory IN (@Territory)
Order by CalendarYear, CalendarMonthNo
END
June 27, 2011 at 9:22 am
You can't put a list (2011, 2012) into an int!
If you want to select multiple years, you will need to look at changing your stored proc to accept a varchar instead and then split the varchar into multiple integers within the proc.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 27, 2011 at 9:24 am
mister.magoo (6/27/2011)
You can't put a list (2011, 2012) into an int!If you want to select multiple years, you will need to look at changing your stored proc to accept a varchar instead and then split the varchar into multiple integers within the proc.
SSRS people get hit with this all the time. If you run that exact same code as a query in the datasource it works, but as soon as you copy this to a proc it fails. :crazy:
June 27, 2011 at 9:37 am
You can't put a list (2011, 2012) into an int!
If you want to select multiple years, you will need to look at changing your stored proc to accept a varchar instead and then split the varchar into multiple integers within the proc.
MM
OK, I understand the first part, but what exactly do I do in order to split the varchar into multiple integers?
Sorry for my ignorance, very new at this.
June 27, 2011 at 9:45 am
June 27, 2011 at 9:46 am
Also, http://www.sqlservercentral.com/articles/T-SQL/73838/
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 27, 2011 at 9:47 am
Oh and while you are at it, you might want to apply the same approach to your other IN statements...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply