Stored procedures and variables

[tvbruwae]

Hi

We have successfully migrated a SQL 6.5 stored procedure to SQL 2000. This SP is called from an ASP web page, which also passes some parameters. However we are having problems using these parameters in a select clause.

Basically the SP looks like this (at least the relevant parts):

CREATE PROCEDURE GetArticles (@Article varchar(16)="",..., @ArtText varchar(40) = "") AS
....
Select <...> where (Articles.ArtText like @ArtText)

This last line used to work in 6.5 but now we are having problems with the "like" structure. Basically the @ArtText parameter contains a string (empty or not) with a %-sign both in front and at the end.
So it looks like %%, %test%, etc. Unfortunately we get no data at all when executing the SP. We tried different approaches with the quotes (add single quotes to the parameter, but quotes around @ArtText, etc.) but nothing seems to work. Does anyone know how to make a like-clause work with a parameter?

Thanks!

 

[dbomrrsm]

Try:

like '%'+@ArtText+'%'

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbomrrsm]

declare @text varchar (20)
set @text = 'Gand'
select * from dbo.Brotherhood_of_the_Ring where [name] like '%'+@text+'%'

returns one row from Brotherhood_of_the_Ring where the name is Gandalf.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[tvbruwae]

Hi

The code you gave me seems to work indeed. However we get strange results when performing some similar tests..

1. When defining a new variable with a static value (like in your example), the query is returning data successfully. This also works when adding the %-signs to the variable (set @text = '%test%'). Still, it doesn't work with the @ArtText parameter

2. When I define the @ArtText parameter with a static value inside the ASP page (so not populated from a form), the query still doesn't work

3. When I simply change the Select clause inside the SP to: "select @ArtText" I get the correct output: "%test%" or "test" (depending on the value I give it inside the ASP page). So it's not the value of the parameter that is causing the select clause to fail..

Any tips on what to check next?

 

[dbomrrsm]

can you paste the code that fails ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[tvbruwae]

We found a solution and it seems that we were looking on the wrong side of the application. The thing we changed is inside the ASP code that calls the stored procedure.

This used to be the line creating the parameter:

ParamList.Append GetParts.CreateParameter ("ArtText", adChar, adParamInput, 40, "%" & Request.Form ("ArtDesc") & "%")

What we did is change the datatype from "adChar" to "adVarChar". According to what we found on the internet the adVarChar type is a string type "reserved for parameter objects". So both types are string types, but apparently something is different regarding the way the ASP code passes the actual value to the SP.. As the code used to work on an NT4/IIS4/SQL 6.5 server we are still not sure what made the code fail though (either SQL 2000, Windows 2003 or IIS6).

Many thanks for your advice on this!

 

[dbomrrsm]

No problem - glad you got it sorted

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]