Stored Procedure Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure Problem

Author

Topic

kwilliams

194 Posts

Posted - 2006-06-23 : 10:38:42

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
-------------------
It's the end of the world as we know it...and I feel fine

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-23 : 10:55:22

If this is a cut and paste of your code, it should not even run.

spBlock @Par1 LIKE '15%', @Par2 = 'ACORN LN', @Par3 = '2006'

Is it actually this? If so, run that in query analyzer and see what your results are.

spBlock @Par1 = '15%', @Par2 = 'ACORN LN', @Par3 = '2006'

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

kwilliams

194 Posts

Posted - 2006-06-23 : 11:02:25

Actually, that was a misstep on my part. It's hard to attach code that's neutral without some mistake. Anyway, this is how the SP actually reads:

spBlock @Par1 = '15%', @Par2 = 'ACORN LN', @Par3 = '2006'

Thanks for the heads-up.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

kwilliams

194 Posts

Posted - 2006-06-23 : 12:57:48

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
-------------------
It's the end of the world as we know it...and I feel fine

kwilliams

194 Posts

Posted - 2006-06-23 : 14:39:42

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:)

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources