Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Error on dynamic SQL procedure

Author  Topic 

lior3790
Starting Member

46 Posts

Posted - 2007-04-15 : 08:44:11
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 35
Conversion 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 ON
set QUOTED_IDENTIFIER ON
GO



ALTER 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

begin

declare @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: mainFrm

And(video is Null or video in ( COALESCE('+@fieldPnl+',PlayersPosition.PosId) and PlayerBasicInfoTable.playerId=PlayerPosition.PlayerId))
---- Panel ID: fieldPn'



execute(@query)
end


khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 08:49:18
You will need to convert all integer variable to string before concatenate.

example :
@MinTxt

to
convert(varchar(10), @MinTxt)



KH

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-15 : 23:57:22
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-16 : 00:59:53
why are you even using dynamic sql here? I don't think it's necessary in this case.

looks like you just want to build a dynamic search criteria. read Erland's other article for ways to do that without dynamic sql:

http://www.sommarskog.se/dyn-search.html


www.elsasoft.org
Go to Top of Page
   

- Advertisement -