error with dynamic stored procedure 3

[lorca]

HI I'm trying to use dynamic stored procedures. My where clause is built up in the asp code, i have a stored procedure which calls that where clause and should display the info back to the screen, except i get an error
Incorrect syntax near 'Col004'

here is my stored procedure

CREATE PROCEDURE spTest (@strWhere varchar(255))
AS

    DECLARE @SQLStatement varchar(255)

     SELECT @SQLStatement = "SELECT Col003, Col004, Col005, Col006, Col007, Col008, Col011 FROM LPRLICASE WHERE "
                              + @strWhere
    
    EXEC(@SQLStatement)
GO

here is the asp page where the where clause gets built up, called LPRSearchResults.asp

SchTradingName = Trim(replace(request("SchTradingName"),"'","''"))
SchStreetName = Trim(replace(request("SchStreetName"),"'","''"))
txtLicType = Trim(replace(request("txtLicType"),"'","''"))

'Build strWhere string (clear first):

strWhere = ""

If SchTradingName <> "" Then
	strWhere = strWhere & "Col004 LIKE '%" & SchTradingName & "%'"
else
	strWhere = strWhere & "(Col004 LIKE '%%' or Col004 is null)"
End If

If SchStreetName <> "" Then

	If strWhere <> "" Then
		strWhere = strWhere & " AND "
	End If

	strWhere = strWhere & "Col005 LIKE '%" & SchStreetName & "%'"

else
	If strWhere <> "" Then
		strWhere = strWhere & " AND "
	End If
	strWhere = strWhere & "(Col005 LIKE '%%'  or Col005 is null)"
End If

If txtLicType <> "" and txtLicType <> "-- All Licence Types --" Then

	If strWhere <> "" Then
		strWhere = strWhere & " AND "
	End If

	strWhere = strWhere & "Col003 LIKE '%" & txtLicType & "%'"

else
	If strWhere <> "" Then
		strWhere = strWhere & " AND "
	End If
 
	strWhere = strWhere & "(Col003 LIKE '%%' or Col003 is null)"
End If

here is the main asp page that calls the other asp page plus stored procedure

%>
<!--#INCLUDE VIRTUAL="/training48/includes/LPR/LPRSearchResults.asp"-->
<%

'set my connections etc then call the sp as below

objCon.Execute "spTest " & strWhere

thanks
rich

 

[sweth]

Try the following to troubleshoot
Response.Write strwhere and see it is printing correctly
use single quote for dynamic sql string
give a space near where clause
and print the @SqlStatment and execute it in query analyser


CREATE PROCEDURE spTest (@strWhere varchar(255))
AS

DECLARE @SQLStatement varchar(255)

SELECT @SQLStatement = 'SELECT Col003, Col004, Col005, Col006, Col007, Col008, Col011 FROM LPRLICASE WHERE '+ @strWhere

EXEC(@SQLStatement)
GO

Thanks and Regards
siva

 

[rac2]

Maybe

objCon.Execute "spTest '" & strWhere & "'"

 

[rac2]

and

strWhere = strWhere & "Col004 LIKE ''%" & SchTradingName & "%''"

 

[lorca]

thank you

I've almost got it now - i have response.write strWhere, copied it into Query Analyser as

exec spTest "Col004 LIKE '%wheat%' AND Col005 LIKE '%a%' AND (Col003 LIKE '%%' or Col003 is null)"

and it works !

still doesn't work calling through my asp though, i get an error 'Incorrect syntax near '%'.' which i get in query analyser if i replace the double quotes with single quotes !

SP now

CREATE PROCEDURE spTest (@strWhere varchar(255))
AS

    DECLARE @SQLStatement varchar(255)

     SELECT @SQLStatement = 'SELECT Col003, Col004, Col005, Col006, Col007, Col008, Col011 FROM LPRLICASE WHERE ' + @strWhere
    
    EXEC(@SQLStatement)
GO

asp call

objCon.Execute "spTest '" & strWhere & "'"

thanks

 

[SimonSellick]

I think that you need more single quotes. The argument to your spTest is a string, so it needs to be wrapped in single quotes, and any occurrence of a single quote within it needs to be replaced with two single quotes - so the submitted line in your example should look like:

exec spTest 'Col004 LIKE ''%wheat%'' AND Col005 LIKE ''%a%'' AND (Col003 LIKE ''%%'' or Col003 is null)'

 

[SimonSellick]

...and what does LIKE '%%' do - is it the same as LIKE '%'? Actually, why do you need to add columns that you are not interested in to the WHERE clause?

Simon

 

[lorca]

thanks simon - i ave got it going now, but yes you're right i probably need to revisit how i build up my strWhere clause.............one strange thing now is i had a select case statement in my asp which worked but now has stopped working ? presume its because i'm using single quotes.

my sp is............................

CREATE PROCEDURE spResults (@strWhere varchar(255))
AS

    DECLARE @SQLStatement varchar(255)
    DECLARE @strOrder varchar(255)
    set @strOrder = " order by Col003 DESC"

     SELECT @SQLStatement = 'SELECT Col003, Col004, Col005, Col006, Col007, Col008, Col011 FROM LPRLICASE WHERE ' + @strWhere + ' ' + @strOrder
    
    EXEC(@SQLStatement)
GO

i open the recdordset as so

strSQL1 = "spResults '" & strWhere & "'"
objRS.open strSQL1,objCon

returns my records ok.............but in my asp i have a select case as so, which now fails ?

select Case objRS("Col007")
Case "VAR"
	LPRResultsBody = LPRResultsBody & "<td class=""LPRtd"">Variation</td>"
Case "NEW"
	LPRResultsBody = LPRResultsBody & "<td class=""LPRtd"">New</td>"
Case "TRN"
	LPRResultsBody = LPRResultsBody & "<td class=""LPRtd"">Transfer</td>"
Case "REN"
	LPRResultsBody = LPRResultsBody & "<td class=""LPRtd"">Renewal</td>"
Case "REV"
	LPRResultsBody = LPRResultsBody & "<td class=""LPRtd"">Review</td>"
Case else
	LPRResultsBody = LPRResultsBody & "<td class=""LPRtd"">" & objRS("Col007") &"</td>"
end select

thanks

 

[SimonSellick]

lorca,

1) Fails how?

2) Make sure that your SQL variable is big enough to hold the SQL, where and order by - your where clause could get quite big.

3) Might be a red herring, but I've had trouble with re-reading a field from a recordset in an ASP page; if I'm going to need to refer to it more than once (like your Select Case and Case else), I put it into a variable and use that. But that is in javascript; it might be different in VB.

Simon

 

[lorca]

Hi Simon

yeah it always runs the case else part of the select case. it worked ok before ?!
I've checked the variable length and that is varchar(2000) so defo long enough, and i've done a response.write objRS("Col007") and it outputs as VAR on the screen ?

strange one ?

thanks

 

[SimonSellick]

Hi lorca,

Again, this is more javascript than VB, but you might try assigning objRS("Col007") to a string variable before the various Case comparisons. I've found in javascript that the values returned in recordsets aren't equal to other strings when using the "==" comparison, but match if first put into a string variable. Actually, this applied to comparing two variables (rs field and string), not a variable and a string constant, but it might be worth a try.

Good luck,
Simon.

 

[lorca]

thanks simon

found some extra padding from the imported file, so i used trim(objRS("Col007")) and its now ok !

thanks for your time