Stored procedure ELSE IF not supported in "SQL Data Source"?

[Wojciech]

Hi, I have stored procedure which is splitted on 2 parts.

CREATE PROCEDURE dbo.MyProcedure
@ReportPart smallint
AS
IF @ReportPart = 0
BEGIN
SELECT ColumnPart0Id, ColumnPart0Name, ColumnPart0Code
FROM Table
END
ELSE IF @ReportPart = 1
BEGIN
SELECT ColumnPart1Address, ColumnPart1PostCode, ColumnPart1Contractor
FROM Table
END

When I execute procedure in Telerik Report Designer (SQL Data Source) with

EXEC dbo.MyProcedure
@ReportPart = 0

everything is ok, but after execute query

EXEC dbo.MyProcedure
@ReportPart = 1

I have error "Can't setup column 'ColumnPart0Name'. This value violates limit MaxLength of this column".

It's weird because in @ReportPart = 1 there isn't any column name like ColumnPart0Name, it exists in first "IF" .

Is it bug?

Query works fine in SSMS.

 

[Neli]

Hi Wojciech,

I tried replicating the issue on my end with the AdventureWorks database, however, I was not able to reproduce the problem. Also, it seems that this is the first time that we are reproducing such a problem.

For that reason, may I ask you to send us a sample runnable report which is connected to the AdventureWorks database or you can send us a backup of the database that reproduces the issue, so I can investigate the problem locally?

[Wojciech]

Here is example of procedure on AdventureWorks database:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ProcedureConditionsTest]
@ReportPart SMALLINT = 1
AS
BEGIN
SET NOCOUNT ON;
IF @ReportPart = 1
BEGIN
SELECT P.FirstName AS FirstNamePart1, P.MiddleName AS MiddleNamePart1, P.LastName AS LastNamePart1,
	A.AddressLine1 AS AddressLine1Part1, A.AddressLine2 AS AddressLine2Part1, A.City AS CityPart1, A.PostalCode AS PostalCodePart1,
	SP.StateProvinceCode AS StateProvinceCodePart1, SP.CountryRegionCode AS CountryRegionCodePart1, SP.[Name] AS NamePart1
FROM [Person].[Person] AS P
JOIN [Person].[BusinessEntityAddress] AS BEA ON P.BusinessEntityID = BEA.BusinessEntityID
JOIN [Person].[Address] AS A ON BEA.AddressID = A.AddressID
JOIN [Person].[StateProvince] AS SP ON A.StateProvinceID = SP.StateProvinceID
WHERE P.FirstName BETWEEN 'A. Scott' AND 'Justine'
ORDER BY P.FirstName, P.LastName
END
ELSE IF @ReportPart = 2
BEGIN
SELECT P.FirstName AS FirstNamePart2, P.MiddleName AS MiddleNamePart2, P.LastName AS LastNamePart2,
	A.AddressLine1 AS AddressLine1Part2, A.AddressLine2 AS AddressLine2Part2, A.City AS CityPart2, A.PostalCode AS PostalCodePart2,
	SP.StateProvinceCode AS StateProvinceCodePart2, SP.CountryRegionCode AS CountryRegionCodePart2, SP.[Name] AS NamePart2
FROM [Person].[Person] AS P
JOIN [Person].[BusinessEntityAddress] AS BEA ON P.BusinessEntityID = BEA.BusinessEntityID
JOIN [Person].[Address] AS A ON BEA.AddressID = A.AddressID
JOIN [Person].[StateProvince] AS SP ON A.StateProvinceID = SP.StateProvinceID
WHERE P.FirstName BETWEEN 'K.' AND 'Zoe'
ORDER BY P.FirstName, P.LastName
END
END
GO

In report Designer I added two SQL Data Source named Part1 and Part2:

Part1:

EXEC [dbo].[ProcedureConditionsTest]
@ReportPart = 1

Part2:

EXEC [dbo].[ProcedureConditionsTest]
@ReportPart = 2

On both sources I have column sufix Part1.

[Neli]

Hi Wojciech,

Thank you for the provided scripts. I tested with the latest version of Telerik Reporting - 16.0.22.225. However, I am afraid that I was not able to reproduce the problem. Please, check the attached video.

 

[Wojciech]

I can't see any video.

[Neli]

Hi Wojciech,

Probably, there was a problem during uploading.

[Wojciech]

I watched Your video. Look at the column names in @ReportPart = 2. It's still named with suffix Part1, should be Part2.

[Wojciech]

Also in this example we have similiar data in both confitions (only columns name are different and WHERE clause). When number of columns are different and data type also it causes errors.

[Neli]

Hi Wojciech,

Thank you for the provided additional details. I was able to reproduce the behavior in design time. However, in the preview, the report works as expected. Please, check the attached report. In general, the data is returned by the data provider, still, I will discuss the case with our development team and let you know if something can be done on our end. Meanwhile, can you please provide us with more details about your scenario and why do you apply this approach?

[Wojciech]

Hi, as I said in the last comment, when columns have different types in each condition then preview doesn't work. I use this scenario because I like to have one procedure for one report. It's easier to correct bugs in reports. I don't have to export/import report to app every time.

[Neli]

Hi Wojciech,

Thank you for your update.

I discussed the case with one of our developers and we will consider the current behavior as a bug in our system. For that reason, I logged it - The Schema is not returned correctly when the SP returns different query depending on a parameter and as a token of gratitude, I updated your Telerik points.

 

[Wojciech]

I think 

     IF 1=0 BEGIN
       SET FMTONLY OFF
     END

in procedure fixed that for me.