Stored Procedure Index Out of Bounds

Stored Procedure Index Out of Bounds

  

Hi

I have a Stored Procedure that goes something like this:

SyntaxEditor Code Snippet

IF EXISTS (SELECT 1 
            FROM --Some query--) 
  BEGIN
    SELECT 'X' AS COVERED_BI_PD 
    SELECT '' AS NOTCOV_BI_PD 
  END
  ELSE
  BEGIN
    SELECT 'X' AS NOTCOV_BI_PD 
    SELECT '' AS COVERED_BI_PD 
  END

Basically I want the atribute to be X if the query is found and null if not. I have the correct output sturcture:


However when the procedure runs i get the following error:

Error in advanced query GetCoverages in GetPolicyCoverages in Forms in TSP_Policies (/* %LogicalDatabase% = GetLogicalDatabase({pv_header}) */  EXEC dbo.usp_PP_DEC_115_SECTION @id_pv): Could not assign '' to 'usp_PP_DEC_115_SECTION.COVERED_BI_PD'... Index was outside the bounds of the array.


I have tried running a SQL Advanced Query with just this piece of code:

SELECT 'X' AS COVERED_BI_PD 

SELECT '' AS NOTCOV_BI_PD

And I am getting the exact same error. Unfortunately I am unable to share the OML. Any sugestions? 


Thanks in advance,

  CLSJ

Solution

Hi,

Have you tried using a single SELECT?

 SELECT 'X' AS COVERED_BI_PD, 
        '' AS NOTCOV_BI_PD
Solution

Eduardo Jauch wrote:

Hi,

Have you tried using a single SELECT?

 SELECT 'X' AS COVERED_BI_PD, 
        '' AS NOTCOV_BI_PD


Hi!

Yes that works, and in the last instance I will have them modify the procedure. Meanwhile I will keep trying to get it to work without the modification. Any idea why this might be happening? Thanks so much!


CLSJ

Hi,

It's because you is doing two distinct selects, with a single column each, but your structure needs two values (than the out of index).

The way your SP is defined, you will not be able to retrieve both values in OutSystems. There is simply no way to do it work changing something in OutSystems side.

A change in the SP is mandatory (as far as I know).

Cheers

Eduardo Jauch

Eduardo Jauch wrote:

Hi,

It's because you is doing two distinct selects, with a single column each, but your structure needs two values (than the out of index).

The way your SP is defined, you will not be able to retrieve both values in OutSystems. There is simply no way to do it work changing something in OutSystems side.

A change in the SP is mandatory (as far as I know).

Cheers

Eduardo Jauch

Ok, I'll take it into account.Thanks!