2 replies to this topic

[TheWebGuy]

This is driving me crazy

I run this query in the sql management studio and it works fine. but seems like it doesn't work in the stored procedure

Can I not use a LIKE '%' in an integer field?

here is my stored procedure

USE [ReefJunkies]

GO

/****** Object:  StoredProcedure [dbo].[SearchLibraryFish]    Script Date: 11/06/2010 21:22:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SearchLibraryFish]


@CommonName Varchar(max),

@ReefCompatibilityID int,

@ReefCareLevelID int,

@ReefFamilyID int,

@FishTypeID int,

@ReefSpeciesID int,

@ReefDietID int,

@ReefTemperamentID int



AS


SET @CommonName= '%' + @CommonName + '%'

SET @ReefCompatibilityID= @ReefCompatibilityID + '%'

SET @ReefCareLevelID= @ReefCareLevelID + '%'

SET @ReefFamilyID= @ReefFamilyID + '%'

SET @FishTypeID= @FishTypeID + '%'

SET @ReefSpeciesID= @ReefSpeciesID + '%'

SET @ReefDietID= @ReefDietID + '%'

SET @ReefTemperamentID= @ReefTemperamentID + '%'


SELECT *

FROM  FishLibrary INNER JOIN

               FishType ON FishLibrary.FishTypeID = FishType.FishTypeID INNER JOIN

               ReefCompatibility ON FishLibrary.ReefCompatibilityID = ReefCompatibility.ReefCompatibilityID INNER JOIN

               ReefCareLevel ON FishLibrary.ReefCareLevelID = ReefCareLevel.ReefCareLevelID INNER JOIN

               ReefFamily ON FishLibrary.ReefFamilyID = ReefFamily.ReefFamilyID INNER JOIN

               ReefSpecies ON FishLibrary.ReefSpeciesID = ReefSpecies.ReefSpeciesID INNER JOIN

               ReefDiet ON FishLibrary.ReefDietID = ReefDiet.ReefDietID INNER JOIN

               ReefTemperament ON FishLibrary.ReefTemperamentID = ReefTemperament.ReefTemperamentID

WHERE (FishLibrary.CommonName LIKE @CommonName) AND (FishLibrary.ReefCompatibilityID LIKE @ReefCompatibilityID) AND 

               (FishLibrary.ReefCareLevelID LIKE @ReefCareLevelID) AND (FishLibrary.ReefFamilyID LIKE @ReefFamilyID) AND (FishLibrary.FishTypeID LIKE @FishTypeID) AND

                (FishLibrary.ReefSpeciesID LIKE @ReefSpeciesID) AND (FishLibrary.ReefDietID LIKE @ReefDietID) AND 

               (FishLibrary.ReefTemperamentID LIKE @ReefTemperamentID)

[Orjan]

I don't understand why doing an LIKE '%', as that means "always true", and then there is no meaning with it. And, why use it otherwise, say it had worked, if I had an LIKE '1%', it would match on 1, 10, 11, 12, and 12535 as well, it just makes no sence in LIKeing an integer. just use the ordinary operators, =, <, >, <=, >=, <> instead...

[WingedPanther]

Like is used with text fields, not numeric fields. You would have to cast your integer to a string before you can use LIKE with it.