Conversion failed when converting the varchar value '%' to data type int.

[vertigo262]

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)

[gpl]

Like '%...' is a string comparison -- how is one number like another ?

To make this work, you would have to convert the integers to strings, then make the comparison.