Passing a parameter into a LIKE command in a stored procedure...

Hi All,
I am having quite a bit of trouble getting a certain stored procedure to
work properly. In my web page I have a list of checkboxes that post to the
url. When I retrieve the value for the "beds" checkbox list from the url it
looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
Proc and use a LIKE command to find any property where (beds LIKE
'[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
parameter gets passed into the stored procedure from the web page, it gives
no errors and does not retrieve any properties. Please take a look at my
stored procedure below and tell me where I may be going wrong. I've
simplified it so that it is less convoluded
CREATE PROCEDURE [sp_SearchResults_v2]
(@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
nvarchar(2), @.a3 nvarchar(2),
@.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
nvarchar(2), @.a9 nvarchar(2),
@.a10 nvarchar(2), @.Kids int)
AS
--declare area variables--
DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
@.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
-- check for null entries and convert to a select all entry Area --
IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null) AND
(@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND (@.a9
is null) AND (@.a10 is null))
BEGIN
set @.area1 = N'%'
set @.area2 = N''
set @.area3 = N''
set @.area4 = N''
set @.area5 = N''
set @.area6 = N''
set @.area7 = N''
set @.area8 = N''
set @.area9 = N''
set @.area10 = N''
END
ELSE
BEGIN
set @.area1 = @.a1
set @.area2 = @.a2
set @.area3 = @.a3
set @.area4 = @.a4
set @.area5 = @.a5
set @.area6 = @.a6
set @.area7 = @.a7
set @.area8 = @.a8
set @.area9 = @.a9
set @.area10 = @.a10
END
SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag, Archive_Flag,
Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
Property_Address, Property_Country,
Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
Dwelling_Code, Pet_Type_code,
Suite_Type_Code, Date_avalible, Property_Rental_Period,
Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
Property_phone2,
Property_ext, First_name, Last_name, Email_address,
CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
Property_houseno, Property_stdesignation,
Property_stname, cross_stno, cross_stname, cross_stdesignation,
CorrectedPhone1, CorrectedPhone2,
Property_ID
FROM dbo.Propeties
WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
AND
(Property_Bedrooms LIKE @.beds)
AND
(City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code = @.area3 OR City_Area_Code = @.area4
OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code = @.area7 OR
City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code = @.area10)
AND
(Kids_results = 1)
AND
(Prop_Del_Flag <> 1)
ORDER BY Rent_Amount
GONow I don't have table descripts etc. so I made a very simple proc.
create proc dbo.NEW_Procedure @.input as varchar(250)
AS
select
ArtistName
,SongName
,Genre
,FileName
,Duration
,FirstLetter
,AlbumName
from RadioFreeLlloyd.dbo.SongInfo
where ArtistName like @.input
When I input any value with either wildcards or not it works. Perhaps you
could create a test table with the same columns as you have variables and
then insert a row when a call is made to the proc. It would seem that that
the values of the parameters are perhaps not what you think.
"Fabio Papa" <fapapa@.hotmail.com> wrote in message
news:8YfPb.2394$2j7.752@.clgrps13...
> Hi All,
> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to
the
> url. When I retrieve the value for the "beds" checkbox list from the url
it
> looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
> Proc and use a LIKE command to find any property where (beds LIKE
> '[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
> LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
> parameter gets passed into the stored procedure from the web page, it
gives
> no errors and does not retrieve any properties. Please take a look at my
> stored procedure below and tell me where I may be going wrong. I've
> simplified it so that it is less convoluded
> CREATE PROCEDURE [sp_SearchResults_v2]
> (@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
> nvarchar(2), @.a3 nvarchar(2),
> @.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
> nvarchar(2), @.a9 nvarchar(2),
> @.a10 nvarchar(2), @.Kids int)
> AS
> --declare area variables--
> DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
> nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
> @.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
> -- check for null entries and convert to a select all entry Area --
> IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null)
AND
> (@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND
(@.a9
> is null) AND (@.a10 is null))
> BEGIN
> set @.area1 = N'%'
> set @.area2 = N''
> set @.area3 = N''
> set @.area4 = N''
> set @.area5 = N''
> set @.area6 = N''
> set @.area7 = N''
> set @.area8 = N''
> set @.area9 = N''
> set @.area10 = N''
> END
> ELSE
> BEGIN
> set @.area1 = @.a1
> set @.area2 = @.a2
> set @.area3 = @.a3
> set @.area4 = @.a4
> set @.area5 = @.a5
> set @.area6 = @.a6
> set @.area7 = @.a7
> set @.area8 = @.a8
> set @.area9 = @.a9
> set @.area10 = @.a10
> END
> SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag,
Archive_Flag,
> Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
> Property_Address, Property_Country,
> Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
> Dwelling_Code, Pet_Type_code,
> Suite_Type_Code, Date_avalible,
Property_Rental_Period,
> Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
> Property_phone2,
> Property_ext, First_name, Last_name, Email_address,
> CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
> Property_houseno, Property_stdesignation,
> Property_stname, cross_stno, cross_stname, cross_stdesignation,
> CorrectedPhone1, CorrectedPhone2,
> Property_ID
> FROM dbo.Propeties
> WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
> AND
> (Property_Bedrooms LIKE @.beds)
> AND
> (City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code
=> @.area3 OR City_Area_Code = @.area4
> OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code => @.area7 OR
> City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code => @.area10)
> AND
> (Kids_results = 1)
> AND
> (Prop_Del_Flag <> 1)
> ORDER BY Rent_Amount
> GO
>|||Fabio Papa (fapapa@.hotmail.com) writes:
> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to
> the url. When I retrieve the value for the "beds" checkbox list from
> the url it looks something like "1,3,5,7". Now I want to pass this into
> a SQL Stored Proc and use a LIKE command to find any property where
> (beds LIKE '[1,3,5,7]'). When I use the query analyzer and manually
> type "WHERE beds LIKE '[1,3,5,7]'" it works fine and pulls up
> properties, but when the parameter gets passed into the stored procedure
> from the web page, it gives no errors and does not retrieve any
> properties. Please take a look at my stored procedure below and tell me
> where I may be going wrong. I've simplified it so that it is less
> convoluded
Maybe you simplifed too much? Save the spelling error for the table
it looks OK, but I don't know what values you are passing.
I would suggest that you trim the conditions until you get values
back. It may not be the LIKE expression that is causing you headache.
> CREATE PROCEDURE [sp_SearchResults_v2]
Ah, by the way, the sp prefix is reserved for system procedures and
SQL Server will first look for these names in master. You should not
use it for your own names.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp