sql query doesn't work after changing to stored procedure

Hello,

I have been learning a .asp for work, and sql server at the same time. The senior software engineer wanted me to change all my queries from query statements to stored procedures.

I was able to create the SP with no problem inside Query Analyzer, but when calling it from ASP I don't get any returns. I don't get any errors, and the rest of my page loads just fine, but I just don't get any search results.

Here is my ASP code:

<%
  WITH sConn
  Set sRs=.Execute(sqlStatement)
  If Not sRs.EOF Then 
    While Not sRs.EOF
     id = sRs("ArticleID")
     name = sRs("articleName")
     pubdate = sRs("articlePubDate")
          
     Response.Write "<a href=../Articles/page.asp?id=" &_
			   id & ">" & name & " -- Published: " & pubdate & "</a><br/>"

      sRs.MoveNext
    WEnd
  End If
  sRs.Close
  Set sRs=Nothing
 
.Close
End With
Set sConn=Nothing
%>

Code (asp):

sqlStatement is:

sqlStatement = "kbSearch 'wField', 'wCondition'"

Code (asp):

The sqlStatement used to be:

sqlStatement = "SELECT ArticleID, articleName, articlePubDate FROM articles WHERE wField like '%wCondition%'" 

Code (asp):

It worked fine in that format, but now it does not.

My stored procedure is:

create PROCEDURE kbSearch
(
  @whfd varchar(100),
  @condition varchar(255)
)
AS
SELECT ArticleID, articleName, articlePubDate
FROM articles
WHERE @whfd like '%@condition%'

GO

Code (sql):

If anyone can help it would really be appreciated.

Thanks,
Johnie Karr
COX Communications
Communication Specialist

 

quite simple, the Like operator cannot compare dates,
change your query.

see this post :
forums.digitalpoint.com/showthread.php?t=566161

 

orielo,

Thanks for the suggestion, but I am not searching the databse by the date field, that is simply a column that I am retrieving.

The three search options that I allow the user to choose from are:

articleBody
articleTitle
articleSystem

I will look at the link you posted though.

Thanks,
Johnie Karr

 

Ok, so I finally broke down and called our Senior Software Engineer, and he helped me figure it out....well, he figured it out for me and I watched.

Anyhow, there was a problem with my SP syntax, and the syntax in ASP to call it.

It should look like this:

CREATE PROCEDURE [Csi].[kbSearch]  
(  
  @whfd varchar(100),  
  @condition varchar(255)  
)  
AS begin  
  
 set nocount on  
  
 execute('SELECT ArticleID, articleName, articlePubDate  
    FROM articles  
    WHERE ' + @whfd + ' like ''%' + @condition + '%''')  
end

Code (sql):

The problem with my SP was mainly that it was seeing @whfield and @condition as a text string, not a variable.

My asp code should look like this:

sqlStatement = "kbSearch '" & wField & "', '" & wCondition & "'"

Code (asp):

Again, same issue, the wy I had it written, it was looking for exactly wField, and not seeing it as a variable.

I sure hope I'm not the first Newbie to make this mistake.

Thanks,
Johnie Karr