Stored Procedure Problem

[kwilliams]

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 variablesDim 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 ValuesDim strVar1 As String, strVar2 As String, strVar3 As String'Manipulate form ValuesIf 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 = '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 = True2) BACKEND:Stored Procedure:CREATE PROCEDURE [dbo].[spBlock]@Par1 varchar (13),@Par2 varchar (24),@Par3 varchar (4)ASSET NOCOUNT ONSELECT *FROM tblBlockWHERE Col1 LIKE @Par1 AND Col2 LIKE @Par2 AND Year = @Par3ORDER BY Col1 ASCSET NOCOUNT OFFGOResulting Stored Procedure on Front-End:spBlock @Par1 = '15%', @Par2 = 'ACORN LN', @Par3 = '2006'(NOTE: Results in partial data - 2 records)Manual Query of Same Data:SELECT *FROM tblBlockWHERE (Col1 LIKE '15%') AND (Col2 = 'ACORN LN') AND (Col3 = '2006')(NOTE: Results in correct data - 4 records)

[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.Col3FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.Col1 = T2.Col1 INNER JOIN TABLE3 T3 ON T1.Col1 = T3.Col1WHERE (T1.Col1 LIKE '15%') AND (T1.Col2 LIKE 'ACORN LN%') AND (T1.Col3 = '2006')ORDER BY T1.Col1I 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. Sorry for the mis-direction.

[kwilliams]

I received a great answer from Jeff Mason in another forum. You can see the thread at http://p2p.wrox.com/topic.asp?TOPIC_ID=46164. It basically had to do with me setting an INNER JOIN vs. a LEFT OUTER JOIN. The latter preserves Table1, and the other empty table(s) return null values from the query. Thanks Jeff:)