Hi,i wrote this procedure but each time i execute it from the sql management i get this error quote: Msg 245, Level 16, State 1, Procedure tempBasicSearchProcedure_Please_Delete, Line 35Conversion failed when converting the varchar value 'SELECT *, HasMedia(p.playerId) as video FROM PlayerBasicInfoTable p ,TeamTable ,MediaTable ,PlayersPosition WHERE ((PlayerBasicInfoTable.Name is Null or PlayerBasicInfoTable.Name like ISNULL("name", "%"))And (PlayerBasicInfoTable.NationalityId is Null or PlayerBasicInfoTable.NationalityId = COALESCE(' to data type int.
here is the code"set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[tempBasicSearchProcedure] @NameTxt VarChar(50) ='%',@Nationality Int =NULL,@MinTxt Int =NULL,@MaxTxt Int =NULL,@SelectCountry Int =NULL,@SelectTeam Char(50) ='%',@EUNationality Bit =NULL,@MinHeightTxt Int =NULL,@MaxHeightTxt Int =NULL,@MinWeightTxt Int =NULL,@MaxWeightTxt Int =NULL,@NationalTeamAppearance Int =NULL,@Video BigInt =NULL,@fieldPnl Int =NULL,@EndOfWhereParam varchar(200)='%' as begindeclare @query nvarchar(500)set @query='SELECT *, HasMedia(p.playerId) as video FROM PlayerBasicInfoTable p ,TeamTable ,MediaTable ,PlayersPosition WHERE ((PlayerBasicInfoTable.Name is Null or PlayerBasicInfoTable.Name like ISNULL("'+@NameTxt+'", "%"))And (PlayerBasicInfoTable.NationalityId is Null or PlayerBasicInfoTable.NationalityId = COALESCE('+@Nationality+',PlayerBasicInfoTable.NationalityId))And (PlayerBasicInfoTable.Age is NULL or PlayerBasicInfoTable.Age >= COALESCE('+ @MinTxt+' ,PlayerBasicInfoTable.Age) and '+ @MinTxt+' <= COALESCE( '+@MaxTxt+' ,PlayerBasicInfoTable.Age))And (PlayerBasicInfoTable.NationalityId is Null or PlayerBasicInfoTable.NationalityId = COALESCE('+@SelectCountry+',PlayerBasicInfoTable.NationalityId))And (TeamTable.TeamName is Null or TeamTable.TeamName like ISNULL('+@SelectTeam+', "%")and PlayerBasicInfoTable.TeamId=TeamTable.teamid )And (PlayerBasicInfoTable.EUNationality is Null or PlayerBasicInfoTable.EUNationality = COALESCE('+@EUNationality+',PlayerBasicInfoTable.EUNationality))And (PlayerBasicInfoTable.Height is NULL or PlayerBasicInfoTable.Height >= COALESCE( '+@MinHeightTxt+' ,PlayerBasicInfoTable.Height) and '+@MinHeightTxt+' <= COALESCE( '+@MaxHeightTxt+' ,PlayerBasicInfoTable.Height))And (PlayerBasicInfoTable.Weight is NULL or PlayerBasicInfoTable.Weight >= COALESCE('+ @MinWeightTxt+' ,PlayerBasicInfoTable.Weight) and '+@MinWeightTxt+' <= COALESCE( '+@MaxWeightTxt+' ,PlayerBasicInfoTable.Weight))And (PlayerBasicInfoTable.NationalTeamAppearance is Null or PlayerBasicInfoTable.NationalTeamAppearance = COALESCE('+@NationalTeamAppearance+',PlayerBasicInfoTable.NationalTeamAppearance))And ((MediaTable.PlayerId is Null or MediaTable.PlayerId = COALESCE('+@Video+',MediaTable.PlayerId))and ))---- Panel ID: mainFrmAnd(video is Null or video in ( COALESCE('+@fieldPnl+',PlayersPosition.PosId) and PlayerBasicInfoTable.playerId=PlayerPosition.PlayerId))---- Panel ID: fieldPn'execute(@query)end |