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

0 Answers 43 Views
DataSource SQL DataSources Report Designer (standalone)
Wojciech
Top achievements
Rank 1
Wojciech asked on 20 Mar 2022, 07:07 PM | edited on 20 Mar 2022, 07:09 PM

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
Telerik team
commented on 23 Mar 2022, 03:03 PM

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
Top achievements
Rank 1
commented on 04 Apr 2022, 08:13 AM

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
Telerik team
commented on 07 Apr 2022, 11:55 AM

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
Top achievements
Rank 1
commented on 07 Apr 2022, 12:00 PM

I can't see any video.
Neli
Telerik team
commented on 07 Apr 2022, 12:02 PM

Hi Wojciech,

Probably, there was a problem during uploading.

Wojciech
Top achievements
Rank 1
commented on 07 Apr 2022, 12:15 PM

I watched Your video. Look at the column names in @ReportPart = 2. It's still named with suffix Part1, should be Part2.
Wojciech
Top achievements
Rank 1
commented on 07 Apr 2022, 01:43 PM

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
Telerik team
commented on 12 Apr 2022, 02:54 PM

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
Top achievements
Rank 1
commented on 15 Apr 2022, 07:50 AM

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
Telerik team
commented on 20 Apr 2022, 01:34 PM

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.

 

No answers yet. Maybe you can help?

Tags
DataSource SQL DataSources Report Designer (standalone)
Asked by
Wojciech
Top achievements
Rank 1
Share this question
or