Subscript out of range: '0' after execution of stored procedure (asp-classic)

Question

I have a stored procedure like this:

USE [RK]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[PRC_THEKE_FRAGE_KOMPLETTIERUNG_SELECT] @sgi nvarchar(8), @is_nll bit
AS

if @is_nll = 1

select distinct (s.Ort + ' ' + s.Standort + ' ' + s.Strasse) as place
, ROUND(cast(sum(sort.fragenstatus) as real)/cast(sum(sort1.FragenStatus) as real) *100,0) as prozent
from Standorte s
left join LLThekeStamm stamm on s.S_ID = stamm.S_ID
left join LLFragenStandort sort on stamm.LLT_ID = sort.LLT_ID
left join LLThekeFragen tf on sort.FragenId = tf.FragenID
left join LLFragenStandort sort1 on stamm.LLT_ID = sort.LLT_ID and sort1.FragenStatus = 1
where ISNULL(tf.Aktiv, '') = 1 AND s.NLL_SGI = @sgi
group by (s.Ort + ' ' + s.Standort + ' ' + s.Strasse)

else

select distinct (s.Ort + ' ' + s.Standort + ' ' + s.Strasse) as place
, ROUND(cast(sum(sort.fragenstatus) as real)/cast(sum(sort1.FragenStatus) as real) *100,0) as prozent
from Standorte s
left join LLThekeStamm stamm on s.S_ID = stamm.S_ID
left join LLFragenStandort sort on stamm.LLT_ID = sort.LLT_ID
left join LLThekeFragen tf on sort.FragenId = tf.FragenID
left join LLFragenStandort sort1 on stamm.LLT_ID = sort.LLT_ID and sort1.FragenStatus = 1
where ISNULL(tf.Aktiv, '') = 1
group by (s.Ort + ' ' + s.Standort + ' ' + s.Strasse)

Just to clarify: It might doesn't even matter what the output of this SP is, but just to show that there is a output, I'll attach something for you:

As you can see, the correct alias are set (namend place and prozent).

In my ASP-classic-part, I'm calling this SP. Have a look at this:

sub getLocationPercentages()
'open DB connection
conn.open "something"

dim array_prozente()

'counter-variable 
p1 = 0

SQL = "EXEC PRC_THEKE_FRAGE_KOMPLETTIERUNG_SELECT '" & session("sgi") & "',1"

set rs2 = CreateObject("ADODB.Recordset")
rs2.open SQL,conn,1,3
redim array_prozente(rs2.RecordCount,1)

'fill array with expected values
do while not rs2.EOF
    array_prozente(p1, 0) = rs2("place") ' <-- this is where the error points at
    array_prozente(p1, 1) = rs2("prozent")
    p1 = p1 + 1
    rs2.MoveNext
loop

rs2.close()
conn.close()

end sub

This ends up in this error-message:

Subscript out of range: '0'

Printing the SQL-String however shows the correct syntax. Copying this output to my Query-window results in the correct values beeing shown.

In my view, I'm trying to access it like this:

<% for i = 0 to p1 %>
    <% if array_prozente(i, 0) > "" then %>
        <tr><td><%= array_prozente(i, 0) %></td><td><%= array_prozente(i, 1) %></td></tr>
    <% end if %>
<% next %>

What am I doing wrong?

Short statement

Simple SQL-queries are working as intended, even with recordcount. This just occures (for now) when working with SP.

Answer 1

Not an answer to the ASP side of things but your procedure has multiple execution paths which can lead to some serious performance problems because of plan caching. The upside in this case is that you can massively simplify your procedure and gain consistent performance. Here is how you could modify your procedure to accommodate both paths.

ALTER PROCEDURE [dbo].[PRC_THEKE_FRAGE_KOMPLETTIERUNG_SELECT] @sgi nvarchar(8), @is_nll bit
AS

select distinct (s.Ort + ' ' + s.Standort + ' ' + s.Strasse) as place
, ROUND(cast(sum(sort.fragenstatus) as real)/cast(sum(sort1.FragenStatus) as real) *100,0) as prozent
from Standorte s
left join LLThekeStamm stamm on s.S_ID = stamm.S_ID
left join LLFragenStandort sort on stamm.LLT_ID = sort.LLT_ID
left join LLThekeFragen tf on sort.FragenId = tf.FragenID
left join LLFragenStandort sort1 on stamm.LLT_ID = sort.LLT_ID and sort1.FragenStatus = 1
where ISNULL(tf.Aktiv, '') = 1 AND s.NLL_SGI = case when @sgi = 1 then @sgi else s.NLL_SGI end
group by (s.Ort + ' ' + s.Standort + ' ' + s.Strasse)

Answer 2

Use GetRows() because it is more efficient than what you are doing in your first sub.

sub getLocationPercentages()
    conn.open "something"

    SQL = "EXEC PRC_THEKE_FRAGE_KOMPLETTIERUNG_SELECT '" & session("sgi") & "',1"

    set rs2 = CreateObject("ADODB.Recordset")
    rs2.open SQL,conn,1,3

    if not rs2.eof then
        array_prozente = rs2.getRows()    
        rs2.close()

        if isArray(array_prozente) then
            for i = 0 to ubound(array_prozente, 2)
                response.write "<tr><td>"
                response.write array_prozente(0,i)
                response.write "</td><td>"
                response.write array_prozente(1,i)
                response.write "</td></tr>"
            next
        end if
    else
        err.raise 8, "mypage.asp", "Query did not return results."
    end if

    conn.close()    
end sub