September 28, 2007 at 7:01 am
Dear All
I have the following stored Proc:-
ALTER PROCEDURE dbo.HS_Players_GetPlayers_BY_Criteria
(
@HamTeamsIDint,
@Namenvarchar(100),
@Surname nvarchar(256),
@PosIDint,
@PageIndexint,
@PageSizeint
)
AS
SET NOCOUNT ON
IF @HamTeamsID = 0SET @HamTeamsID = NULL
IF @Name = '0' SET @Name = NULL
IF @Surname = '0' SET @Surname = NULL
IF @PosID = 0SET @PosID = NULL
SELECT * FROM
(
SELECT HS_Players.playerSurname, HS_Players.playerName, HS_Players.fk_hamTeamID, HS_HamTeams.hamTeamName AS HamTeamName, HS_Players.fk_posID,
HS_PlayerPos.positionName AS PositionName, HS_Players.playerDOB, HS_Players.playerEmail, HS_Players.playerPrevClubs, HS_Players.playerProfile,
HS_Players.playerIntApp, HS_Players.playerActive, HS_Players.playerCareer, HS_Players.AddedBy,
ROW_NUMBER() OVER (ORDER BY playerSurname DESC) AS RowNum
FROM HS_Players INNER JOIN
HS_HamTeams ON HS_Players.fk_hamTeamID = HS_HamTeams.hamTeamID INNER JOIN
HS_PlayerPos ON HS_Players.fk_posID = HS_PlayerPos.playerPosID
WHERE (HS_Players.fk_hamTeamID = COALESCE (HS_Players.fk_hamTeamID, @HamTeamsID))
AND (HS_Players.playerName = COALESCE (HS_Players.playerName, @Name))
AND (HS_Players.playerSurname = COALESCE (HS_Players.playerSurname, @Surname))
AND (HS_Players.fk_hamTeamID = COALESCE (HS_Players.fk_hamTeamID, @HamTeamsID))
--WHERE (HS_Players.fk_hamTeamID = COALESCE (HS_Players.fk_hamTeamID, ''))
--AND (HS_Players.playerName = COALESCE (HS_Players.playerName, ''))
--AND (HS_Players.playerSurname = COALESCE (HS_Players.playerSurname, ''))
--AND (HS_Players.fk_posID = COALESCE (HS_Players.fk_PosID, ''))
) HS_Players
WHERE HS_Players.RowNum BETWEEN (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
ORDER BY HS_Players.playerSurname ASC
This stored proc is always returning all the values, no matter what parameters I pass to it.
Can you guys tell me if I have any errors in the COALESCE statements since I am not an expert in SQL Server yet.
Thanks
Johann
September 28, 2007 at 7:21 am
You need to reverse the order in your coalesce's..
You have (column, variable that my be null)
You should have (variable, column)
/Kenneth
September 28, 2007 at 7:31 am
Hi Kenneth
I changed the order, and now its not returning anything
I am entering 1 as HamTeamID and 2 as PosID, and '0' for Name and Surname.
Its supposed to return 2 rows.
October 1, 2007 at 1:56 am
Maybe I misunderstood what you're trying to do...
I assumed that you have a variable that is 'optional', that is the variable may have a value, and if it does, then use that value. If it doesn't have a value, it's null, and then it should behave like there was no variable.. To do that, you may do as I suggested.
t1.column = COALESCE( @variable, t1.column )
The idea here is that if @variable is not null, it will be resolved as
t1.column = @variable
.. if @variable is null, then it will be resolved as
t1.column = t1.column
It looked to me that's what you were trying to do...?
/Kenneth
October 1, 2007 at 2:49 am
Hi Kenneth
What I am trying to do is this.
I am passing 4 parameters in, and if there is any value, then the where clause is supposed to do the where according to the parameters.
However, if any of them is null, then its supposed to ignore them. So if all 4 parameters are null, then the where clause is not active.
How can I achieve that?
Thanks for your help
Johann
October 1, 2007 at 3:52 am
never compare your column values to null because null is not necessarily equal to null
anyway try to change your code like so
IF @HamTeamsID is null SET @HamTeamsID = 0
IF @Name is null SET @Name = ''
IF @Surname is null SET @Surname = ''
IF @PosID is null SET @PosID = 0
and in your where clause replace with this code
where (HS_Players.fk_hamTeamID = @HamTeamsID or @HamTeamsID <> 0)
and (HS_Players.playerName = @Name or @Name <>'')
and (HS_Players.playerSurname = @Surname or @Surname<>'')
and (HS_Players.fk_hamTeamID = @HamTeamsID or @HamTeamsID<>0)
October 1, 2007 at 4:29 am
Hi Kenneth
I changed everything as you told me so the stored proc looks like this now
ALTER PROCEDURE dbo.HS_Players_GetPlayers_BY_Criteria
(
@HamTeamsIDint,
@Namenvarchar(100),
@Surname nvarchar(256),
@PosIDint,
@PageIndexint,
@PageSizeint
)
AS
SET NOCOUNT ON
IF @HamTeamsID is null SET @HamTeamsID = 0
IF @Name is null SET @Name = ''
IF @Surname is null SET @Surname = ''
IF @PosID is null SET @PosID = 0
SELECT * FROM
(
SELECT HS_Players.playerSurname, HS_Players.playerName, HS_Players.fk_hamTeamID, HS_HamTeams.hamTeamName AS HamTeamName, HS_Players.fk_posID,
HS_PlayerPos.positionName AS PositionName, HS_Players.playerDOB, HS_Players.playerEmail, HS_Players.playerPrevClubs, HS_Players.playerProfile,
HS_Players.playerIntApp, HS_Players.playerActive, HS_Players.playerCareer, HS_Players.AddedBy,
ROW_NUMBER() OVER (ORDER BY playerSurname DESC) AS RowNum
FROM HS_Players INNER JOIN
HS_HamTeams ON HS_Players.fk_hamTeamID = HS_HamTeams.hamTeamID INNER JOIN
HS_PlayerPos ON HS_Players.fk_posID = HS_PlayerPos.playerPosID
where (HS_Players.fk_hamTeamID = @HamTeamsID or @HamTeamsID <> 0)
and (HS_Players.playerName = @Name or @Name <>'')
and (HS_Players.playerSurname = @Surname or @Surname<>'')
and (HS_Players.fk_hamTeamID = @HamTeamsID or @HamTeamsID<>0)
) HS_Players
WHERE HS_Players.RowNum BETWEEN (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
ORDER BY HS_Players.playerSurname ASC
and I am passing 1 as HamTeamID, null as Name and Surname, 2 as PosID, 0 as @PageIndex and 999 as @PageSize.
And I am getting the following error:-
Procedure or Function 'HS_Players_GetPlayers_BY_Criteria' expects parameter '@Name', which was not supplied.
October 1, 2007 at 4:39 am
Hi Kenneth
I found my error.
I was repeating the same variable @HamTeamsID, when it was supposed to be @PosID!
so
where (HS_Players.fk_hamTeamID = @HamTeamsID or @HamTeamsID <> 0)
and (HS_Players.playerName = @Name or @Name <>'')
and (HS_Players.playerSurname = @Surname or @Surname<>'')
and (HS_Players.fk_hamTeamID = @HamTeamsID or @HamTeamsID<>0)
has to be amended to
where (HS_Players.fk_hamTeamID = @HamTeamsID or @HamTeamsID <> 0)
and (HS_Players.playerName = @Name or @Name <>'')
and (HS_Players.playerSurname = @Surname or @Surname<>'')
and (HS_Players.fk_posID = @PosID or @PosID<>0)
Thanks for your help!
Johann
October 1, 2007 at 5:01 am
October 1, 2007 at 5:09 am
Now its working fine and returning the correct data, since before, it was filtering with 2 HamTeamsID parameters
So for example it was trying to get HamTeamsID 1 and HamTeamsID 2 and as so, was not returning any results
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply