Problem with ASP and Stored Procedure

[dbrom]

Hey!

On the page I am working on, I use a stored procedure to search actors resumes by gender, ethnicity, age, and keywords.

Here is how my ASP code look like:

resGender = String(Request.Form("resGender&quot);
resAgeRange = String(Request.Form("resAge&quot);
resEthnicity = ( String(Request.Form("resEthnicity&quot) == "undefined" ? "" : String(Request.Form("resEthnicity&quot) );
resKeyword = ( String(Request.Form("resKeyword&quot) == "undefined" ? "" : String(Request.Form("resKeyword&quot) );


cmdSearchResults = Server.CreateObject("ADODB.Command&quot

with (cmdSearchResults)
{
ActiveConnection = connTviDB;
CommandType = adCmdStoredProc;
CommandText = "uspResSimpleSearch";

Parameters.Append(CreateParameter("@resGender",200,1,6,resGender));
Parameters.Append(CreateParameter("@resAgeRange",200,1,25,resAgeRange));
Parameters.Append(CreateParameter("@resEthnicity",200,1,50,resEthnicity));
Parameters.Append(CreateParameter("@resKeyword",200,1,255,resKeyword));
Parameters.Append(CreateParameter("@totalRecs",3,2));
rsSearch = Execute();
//Here I work with recordset...
rsSearch.Close();
totalRecs = cmdSearchResults.Parameters("@totalRecs&quot.Value;
}




Here is the code for sproc:


CREATE PROC uspResSimpleSearch

@resGender varchar(6),
@resEthnicity varchar(50),
@resAgeRange varchar(25),
@resKeyword varchar(255),
@totalRecs int OUTPUT

AS


SELECT resFirstName, resLastName, resumeID, resFilename
FROM resPersonalInfo
WHERE resGender = @resGender AND
resEthnicity LIKE '%' + @resEthnicity + '%' AND
resAgeRange LIKE '%' + @resAgeRange + '%' AND
resKeyword LIKE '%' + @resKeyword + '%'
ORDER BY
resFilename DESC

SELECT @totalRecs = @@ROWCOUNT


Here is my problem:
when I don't specify ethnicity or age, it is passed to sproc as ''. Once there, it would be assembled ('%' + '' + '%') and would match anything.
The problem is that when I don't specify one of the parameters, the search does not return any results.

However, when I use Query Analizer to run a stored procedure from SQL Server environment, everything works...

Here is the code for Query Analizer:

DECLARE @resGender varchar(6)
DECLARE @resEthnicity varchar(50)
DECLARE @resAgeRange varchar(25)
DECLARE @resKeyword varchar(255)
DECLARE @totalRecs int

SET @resGender = 'female'
SET @resEthnicity = 'asian'
SET @resAgeRange = ''
SET @resKeyword = ''

EXECUTE uspResSimpleSearch @resGender,@resEthnicity,@resAgeRange,@resKeyword, @totalRecs


So the question is:
Any ideas why everything works from Query Analizer and does not work from ASP??
Can it be that '%' + @parameterName + '%' is not the way to go? Anyone can think of a better SQL stmt where an empty ('') parameter will not limit search results?

Hope you can help...
:-I
<Dmitriy>
dbrom@crosswinds.net

 

[link9]

My suggestion would be to set a default value for the parameters that aren't always required inside the SPROC, and the dynamically build the SQL statement there based on the results. If you set a default value, then the SPROC doesn't return an error if you don't supply a value:

@val1 int = 0

So that if you supply a value, then it can use it, but if not, then it keeps the default. Then, you can say something like:

IF @val1 <> 0
BEGIN
/*do something here with the value*/
END
ELSE
BEGIN
/*do something else w/o the value*/
END

So that you simply don't include it at all in the inner-workings of your SPROC if you don't need to.


Paul Prewett