Dynamic SQL convertions ETC. - SQL Server Forums

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)

Dynamic SQL convertions ETC.

Author

Topic

lior3790
Starting Member

46 Posts

Posted - 2007-04-18 : 08:38:09

Hi again,

This is my problem:
i'm sending params from a search page to procedure that searches the DB for player information.
because i'm using special components on the page , the way i need to send the params are variously different.

for example:
i need to check for existance of int value of a column with a string value like so

quote:
po.PosId is Null or po.PosId in ( ISNULL(@fieldPnl,po.PosId))

and the @fieldPnl is a varchar represents "50,55,40".

as long as i put one number in the param like so @fieldPnl="55" i have no problem converting. the problem starts when the param eqauls
@fieldPnl="55,33,77" etc.

i have tried two method to solve this problem.
first i tried to play with the column converting it ro varchar. i got no records.

second i change the whole query to an dynamic one so all of the convertions are more playable. still got no records back.

here are the two method i tried:


ALTER PROCEDURE [dbo].[BasicSearchProcedure] 

@NameTxt VarChar(50) ='%',
@Nationality Int =NULL,
@MinTxt Int =NULL,
@MaxTxt Int =NULL,
@SelectCountry Int =NULL,
@SelectTeam nvarChar(50) ='%',
@EUNationality Bit =NULL,
@MinHeightTxt Int =NULL,
@MaxHeightTxt Int =NULL,
@MinWeightTxt Int =NULL,
@MaxWeightTxt Int =NULL,
@NationalTeamAppearance Int =NULL,
@Video bit =NULL,
@fieldPnl varchar(50) =null,
@EndOfWhereParam varchar(200)='%'

as

declare @query varchar(50)

set @query='

SELECT     NameNation.NatPic, t.TeamName, p.PlayerID, p.Name, p.Shirt, 
                      p.Weight, convert(float,(p.Height))/100 as Height, p.Age, PositionTable.PosAbbriv, NameNation_1.NatPic as countryPic,dbo.getplayermediaicon(p.playerId) as video
FROM         PositionTable left JOIN
                      PlayersPosition as po ON PositionTable.PosId = po.PosId left JOIN
                      NameNation left JOIN
                      PlayerBasicInfoTable as p ON NameNation.NationId = p.NationalityId ON 
                      po.PlayerId = p.PlayerID left JOIN
                      TeamTable as t ON p.CurrentTeamId = t.Teamid left JOIN
                      NameNation AS NameNation_1 ON t.TeamCountryId = NameNation_1.NationId left join MediaTable  m on p.playerid=m.playerid

 WHERE 
(
(p.Name is Null or p.Name like ISNULL("%"'+@NameTxt+'"%", "%"))
And  
( p.NationalityId is Null or  p.NationalityId= COALESCE('+Convert(varchar(50),@Nationality)+' ,p.NationalityId))
And  
 
(p.Age is NULL or p.Age >= COALESCE('+Convert(varchar(50),@MinTxt)+'   ,p.Age) AND  p.Age   <= COALESCE('+Convert(varchar(50),@MaxTxt)+' ,p.Age))
And  
(p.NationalityId is Null or p.NationalityId = COALESCE('+Convert(varchar(50),@SelectCountry)+' ,p.NationalityId))
And  
(t.TeamName is Null or t.TeamName like ISNULL('+@SelectTeam+', "%")and p.currentTeamId=t.teamid )
And  
(p.EUNationality is Null or p.EUNationality = COALESCE('+Convert(varchar(50),@EUNationality)+',p.EUNationality))
And  
 
(p.Height is NULL or p.Height >= COALESCE('+Convert(varchar(50),@MinHeightTxt)+' ,p.Height) AND p.Height  <= COALESCE( '+Convert(varchar(50),@MaxHeightTxt)+',p.Height))
And  
 
(p.Weight is NULL or p.Weight >= COALESCE('+Convert(varchar(50),@MinWeightTxt)+',p.Weight) AND p.Weight  <= COALESCE('+Convert(varchar(50),@MaxWeightTxt)+'  ,p.Weight))
And  
(p.NationalTeamAppearance is Null or p.NationalTeamAppearance = COALESCE('+Convert(varchar(50),@NationalTeamAppearance)+',p.NationalTeamAppearance))
And  
(dbo.HasMedia(p.playerId) is Null or dbo.HasMedia(p.playerId) = COALESCE('+Convert(varchar(50),@Video)+',dbo.HasMedia(p.playerId)))


---- Panel ID: mainFrm

And (po.PosId is Null or po.PosId in ( ISNULL('+@fieldPnl+',po.PosId)) AND p.playerId=po.PlayerId))
---- Panel ID: fieldPn'




 exec(@query)

the non-dynamic one:



ALTER PROCEDURE [dbo].[BasicSearchProcedure] 

@NameTxt VarChar(50) ='%',
@Nationality Int =NULL,
@MinTxt Int =NULL,
@MaxTxt Int =NULL,
@SelectCountry Int =NULL,
@SelectTeam nvarChar(50) ='%',
@EUNationality Bit =NULL,
@MinHeightTxt Int =NULL,
@MaxHeightTxt Int =NULL,
@MinWeightTxt Int =NULL,
@MaxWeightTxt Int =NULL,
@NationalTeamAppearance Int =NULL,
@Video bit =NULL,
@fieldPnl varchar(50) =null,
@EndOfWhereParam varchar(200)='%'

as



SELECT     NameNation.NatPic, t.TeamName, p.PlayerID, p.Name, p.Shirt, 
                      p.Weight, convert(float,(p.Height))/100 as Height, p.Age, PositionTable.PosAbbriv, NameNation_1.NatPic as countryPic,dbo.getplayermediaicon(p.playerId) as video
FROM         PositionTable left JOIN
                      PlayersPosition as po ON PositionTable.PosId = po.PosId left JOIN
                      NameNation left JOIN
                      PlayerBasicInfoTable as p ON NameNation.NationId = p.NationalityId ON 
                      po.PlayerId = p.PlayerID left JOIN
                      TeamTable as t ON p.CurrentTeamId = t.Teamid left JOIN
                      NameNation AS NameNation_1 ON t.TeamCountryId = NameNation_1.NationId left join MediaTable  m on p.playerid=m.playerid

 WHERE 
(
(p.Name is Null or p.Name like ISNULL('%'+@NameTxt+'%', '%'))
And  
( p.NationalityId is Null or  p.NationalityId= COALESCE(@Nationality ,p.NationalityId))
And  
 
(p.Age is NULL or p.Age >= COALESCE(@MinTxt   ,p.Age) AND  p.Age   <= COALESCE(@MaxTxt ,p.Age))
And  
(p.NationalityId is Null or p.NationalityId = COALESCE(@SelectCountry ,p.NationalityId))
And  
(t.TeamName is Null or t.TeamName like ISNULL(@SelectTeam, '%')and p.currentTeamId=t.teamid )
And  
(p.EUNationality is Null or p.EUNationality = COALESCE(@EUNationality,p.EUNationality))
And  
 
(p.Height is NULL or p.Height >= COALESCE(@MinHeightTxt ,p.Height) AND p.Height  <= COALESCE( @MaxHeightTxt  ,p.Height))
And  
 
(p.Weight is NULL or p.Weight >= COALESCE(@MinWeightTxt  ,p.Weight) AND p.Weight  <= COALESCE(@MaxWeightTxt  ,p.Weight))
And  
(p.NationalTeamAppearance is Null or p.NationalTeamAppearance = COALESCE(@NationalTeamAppearance,p.NationalTeamAppearance))
And  
(dbo.HasMedia(p.playerId) is Null or dbo.HasMedia(p.playerId) = COALESCE(@Video,dbo.HasMedia(p.playerId)))


---- Panel ID: mainFrm

And (po.PosId is Null or Convert(VarChar(50),po.PosId) in ( ISNULL(@fieldPnl,po.PosId)) AND p.playerId=po.PlayerId))
---- Panel ID: fieldPn

thank you for your time and help,
Lior

lior3790
Starting Member

46 Posts

Posted - 2007-04-18 : 08:50:53

Another thought i had is to declare new param and ,ake the changes only on it but i got logical error.

ALTER PROCEDURE [dbo].[BasicSearchProcedure]

@NameTxt VarChar(50) ='%',
@Nationality Int =NULL,
@MinTxt Int =NULL,
@MaxTxt Int =NULL,
@SelectCountry Int =NULL,
@SelectTeam nvarChar(50) ='%',
@EUNationality Bit =NULL,
@MinHeightTxt Int =NULL,
@MaxHeightTxt Int =NULL,
@MinWeightTxt Int =NULL,
@MaxWeightTxt Int =NULL,
@NationalTeamAppearance Int =NULL,
@Video bit =NULL,
@fieldPnl varchar(50) =null,
@EndOfWhereParam varchar(200)='%'

as

declare @fieldPos varchar(50)
set @fieldPos ='(po.PosId is Null or Convert(VarChar(50),po.PosId) in ( ISNULL('+@fieldPnl+',po.PosId)) AND p.playerId=po.PlayerId)'

SELECT NameNation.NatPic, t.TeamName, p.PlayerID, p.Name, p.Shirt,
p.Weight, convert(float,(p.Height))/100 as Height, p.Age, PositionTable.PosAbbriv, NameNation_1.NatPic as countryPic,dbo.getplayermediaicon(p.playerId) as video
FROM PositionTable left JOIN
PlayersPosition as po ON PositionTable.PosId = po.PosId left JOIN
NameNation left JOIN
PlayerBasicInfoTable as p ON NameNation.NationId = p.NationalityId ON
po.PlayerId = p.PlayerID left JOIN
TeamTable as t ON p.CurrentTeamId = t.Teamid left JOIN
NameNation AS NameNation_1 ON t.TeamCountryId = NameNation_1.NationId left join MediaTable m on p.playerid=m.playerid

WHERE
(
(p.Name is Null or p.Name like ISNULL('%'+@NameTxt+'%', '%'))
And
( p.NationalityId is Null or p.NationalityId= COALESCE(@Nationality ,p.NationalityId))
And

(p.Age is NULL or p.Age >= COALESCE(@MinTxt ,p.Age) AND p.Age <= COALESCE(@MaxTxt ,p.Age))
And
(p.NationalityId is Null or p.NationalityId = COALESCE(@SelectCountry ,p.NationalityId))
And
(t.TeamName is Null or t.TeamName like ISNULL(@SelectTeam, '%')and p.currentTeamId=t.teamid )
And
(p.EUNationality is Null or p.EUNationality = COALESCE(@EUNationality,p.EUNationality))
And

(p.Height is NULL or p.Height >= COALESCE(@MinHeightTxt ,p.Height) AND p.Height <= COALESCE( @MaxHeightTxt ,p.Height))
And

(p.Weight is NULL or p.Weight >= COALESCE(@MinWeightTxt ,p.Weight) AND p.Weight <= COALESCE(@MaxWeightTxt ,p.Weight))
And
(p.NationalTeamAppearance is Null or p.NationalTeamAppearance = COALESCE(@NationalTeamAppearance,p.NationalTeamAppearance))
And
(dbo.HasMedia(p.playerId) is Null or dbo.HasMedia(p.playerId) = COALESCE(@Video,dbo.HasMedia(p.playerId)))

---- Panel ID: mainFrm

And @fieldPos )
---- Panel ID: fieldPn