I have a stored procedure that gets called from an ASP/VB syntax front-end, and a SQL Server 2k back-end, and I'm running into a problem with the results. Basically, I'm trying to get block data from around an address. But I get two records when I should get 4 records.

Below is the code that I have so far. When I do this either through the front-end or directly through the SQL Server Query Analyzer, I get partial results. But when I do this manually through SQL Server, I'm able to get the proper results. I'm stumped, so I'd greatly appreciate any help. Thanks.

1) FRONT-END:

'Declare variables
Dim strStreetNbr_db As String = Session("StreetNbrDb")
Dim strDefaultYear As String = "2006"
Dim strStreetNbr_db_b As String, strPlate_reblock As String, objKillQuery As Object

'Pull in form Values
Dim strVar1 As String, strVar2 As String, strVar3 As String

'Manipulate form Values
If objForm1 = True Then

    'Assign variables
    strStreetNbr_db As String = Trim(Request.Form("strnbr") '***NOTE: Result is "1519" (without quotes)***
    strStreetName_db As String = Trim(Request.Form("strname") '***NOTE: Result is "ACORN LN" (without quotes)***
    strYear As String = strDefaultYear '***NOTE: Result is "2006" (without quotes)***

    'Determine length of address number
    Select Case Len(strStreetNbr_db)
        Case "1"
        strStreetNbr_db = ""
        strStreetNbr_db_b = "_"
        Case "2"
        strStreetNbr_db = ""
        strStreetNbr_db_b = "__"
        Case "3"
        strStreetNbr_db = Left(strStreetNbr_db, 1)
        strStreetNbr_db_b = strStreetNbr_db & "%"
        Case "4"
        strStreetNbr_db = Left(strStreetNbr_db, 2)
        strStreetNbr_db_b = strStreetNbr_db & "%"
        Case "5"
        strStreetNbr_db = Left(strStreetNbr_db, 3)
        strStreetNbr_db_b = strStreetNbr_db & "%"
        Case Else
        objKillQuery = True
    End Select
    '***NOTE: Result is "15%" (without quotes)***

    'Display data
    Response.Write("<table class='medbeborder' width='100%'>")
    Response.Write("<tr class='medbeheader' colspan='5'>")
    Response.Write("<td align='center'>Street #</td>")
    Response.Write("<td align='center'>Street Name</td>")
    Response.Write("<td align='center'>Year</td>")
    Response.Write("</tr>")
    If objKillQuery <> True Then
        '------------------Block Query Begins---------------------
        Dim sqlBlockQuery As Object, rsBlockQuery As Object
        objConn = Server.CreateObject("ADODB.Connection")
        objConn.Open (strConnLandUse)
        sqlBlockQuery = "spBlock @Par1 = '" & strStreetNbr_db_b & "', @Par2 = '" & strStreetName_db & "', @Par3 = '" & strDefaultYear & "'"
        rsBlockQuery = objConn.Execute(sqlBlockQuery)
        'Response.Write("sqlBlockQuery & "<br />") 'TEST
        '***NOTE: Result is spBlock @Par1 LIKE '15%', @Par2 = 'ACORN LN', @Par3 = '2006' ***
        If NOT rsBlockQuery.EOF Then 'If RS is not empty
            Do While NOT rsBlockQuery.EOF 'Begin loop
                intRecordCount = intRecordCount + 1 'Record counter
                If intRecordCount > 200 Then Exit Do 'Exit from infinite loop
                If intRecordCount <= 200 Then
                    'Display results (under 200 records)
                    Response.Write("<td align='center'>" & Trim(rsBlockQuery("StrNbr").Value.ToString) & "</td>")
                    Response.Write("<td align='center'>" & Trim(rsBlockQuery("StrName").Value.ToString) & "</td>")
                    Response.Write("<td align='center'>" & Trim(rsBlockQuery("Year").Value.ToString) & "</td>")
                    Response.Write("</tr>")
                End If
                rsBlockQuery.MoveNext
            Loop 'End loop Do While NOT rsBlockQuery.EOF
        End If
        sqlBlockQuery = nothing
        rsBlockQuery = nothing
        objConn.Close
        objConn = nothing
        intRecordCount = 0 'Reset record counter
        '------------------Block Query Ends-----------------------
    End If 'End If objKillQuery <> True
    Response.Write("</table>")
End If 'End objForm1 = True


2) BACKEND:

Stored Procedure:

CREATE PROCEDURE [dbo].[spBlock]
@Par1 varchar (13),
@Par2 varchar (24),
@Par3 varchar (4)
AS

SET NOCOUNT ON

SELECT *
FROM tblBlock
WHERE Col1 LIKE @Par1 AND Col2 LIKE @Par2 AND Year = @Par3
ORDER BY Col1 ASC

SET NOCOUNT OFF
GO

Resulting Stored Procedure on Front-End:
spBlock @Par1 LIKE '15%', @Par2 = 'ACORN LN', @Par3 = '2006'
(NOTE: Results in partial data - 2 records)

Manual Query of Same Data:
SELECT *
FROM tblBlock
WHERE (Col1 LIKE '15%') AND (Col2 = 'ACORN LN') AND (Col3 = '2006')
(NOTE: Results in correct data - 4 records)

KWilliams

Just doing a quick glance, it seems your SP and the manual query differ in the handling of '@par2'.

The SP tests:

    Col2 LIKE @Par2 (where @Par2 has the value 'ACORN LN')

and your manual query has

    Col2 = 'ACORN LN'

These are not the same thing... Perhaps your @Par2 value should be 'ACORN LN%'?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi Jeff,

I appreciate your response. I checked the SP and manual query out to see what I should change, and this is what I tried:

PARAMETER VALUES:
@Par1 = '15%'
@Par2 = 'ACORN LN%'
@Par3 = '2006

Basically, this is what is rendered through the SP which does not work:
WHERE (Prop_str_nbr LIKE '15%') AND (Prop_str_name LIKE 'ACORN LN%') AND ([Year] = '2006')
(NOTE: Rendered from "WHERE (Prop_str_nbr LIKE @StreetNbr) AND (Prop_str_name LIKE @StreetName) AND ([Year] = @REYear)")

And this is the manual version that does work:
WHERE (Prop_str_nbr LIKE '15%') AND (Prop_str_name LIKE 'ACORN LN%') AND ([Year] = '2006')

As you can see, they're identical. But unfortunately the first one processes a partial 2 records, and the 2nd one processes a full 4 records. So I'm still stumped.

KWilliams

Ok, after some further testing, I've found the problem. It's actually unrelated to the query that we've been using, so that's why I was getting a different result. The entire query contains 3 tables with inner joins, and one of the tables doesn't have data for this property, so it affects the entire query.

This is the entire query that I have:
SELECT T1.Col1, T1.Col2, T1.Col3, T2.Col1, T2.Col2, T2.Col3, T3.Col1, T3.Col2, T3.Col3
FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.Col1 = T2.Col1 INNER JOIN TABLE3 T3 ON T1.Col1 = T3.Col1
WHERE (T1.Col1 LIKE '15%') AND (T1.Col2 LIKE 'ACORN LN%') AND (T1.Col3 = '2006')
ORDER BY T1.Col1

I know that it's a bit confusing, but basically Table3 is empty for this property, so no records show up at all on this query. So within a SQL query, is there a way to use a table with inner joins on tables that may or may not have data within them, because I'm not sure how to do that being a newbie to SP's. I've really appreciated your help, and I'm sorry for the mis-direction.

KWilliams

Study up on OUTER JOINs.

An OUTER JOIN preserves the rows in one of the tables.

As you've seen, an INNER JOIN only returns a row in the resultset if there is a row in both tables which matches the JOIN condition.

An OUTER JOIN returns all of the rows in the "preserved" table, and then those rows in the "other" table which match the join condition. If there is no row in the "other" table, the corresponding values for that table in the resultset are set to NULL.

Consider these two tables:
The LEFT OUTER JOIN signifies that the left hand table (Table1) is the preserved table, so all rows in that table will be present in the resultset. Since Table2 has missing rows, the corresponding column values are returned as NULL.

If I had used a RIGHT OUTER JOIN, then the right-hand table (Table2) would have been the preserved table, so all of its rows would be in the output. The result there would be the same as the INNER JOIN above (why?).

There's also a FULL OUTER JOIN where both tables are preserved.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi Jeff,

You are a Godsend. I did what you said, and it worked great. I really appreciate you explaining joins to me in a simple way, and now I better understand how to set up my queries and stored procedures.

And to answer your question:
That's because if I used a RIGHT OUTER JOIN on a table with empty data, I would be specifying that I want Table2 to be preserved instead of Table1. The result would return no records, just like with the INNER JOIN. See, I think I learned something. Thanks again, and I hope that you have a great weekend. Cheers.

KWilliams

Well, close enough, I guess ...

The reason that the RIGHT OUTER JOIN would have the same results as the INNER JOIN is that for every row in Table2 there is a row in Table1 matching the JOIN condition. Since it's an OUTER JOIN, Table2 is the preserved table, so all rows in Table2 would be returned along with the matching rows in Table1. Since all the rows in Table2 have a matching entry in Table1, the end result is the same as if an INNER JOIN had been done instead, since INNER JOINs only return matching rows.

The point here is that the order of the JOINs is important when you do OUTER JOINs. Table1 INNER JOIN Table2 is the same as Table2 INNER JOIN Table1, but that is not true for OUTER JOINs.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Ok, well thanks for the clarification. That will really help me out in future application development. Talk to you later.

KWilliams