Error in COALESCE stored procedure - Stored Procedures, Triggers, & Functions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » Error in COALESCE stored procedure

Error in COALESCE stored procedure

Options

Previous Topic · Next Topic

monfu

Posted: Friday, September 28, 2007 8:10:57 AM

Rank: Newbie

Joined: 9/28/2007
Posts: 3
Points: 9
Location: Dublin

Dear All

I have the following stored Proc:-

ALTER PROCEDURE dbo.HS_Players_GetPlayers_BY_Criteria
(
@HamTeamsID int,
@Name nvarchar(100),
@Surname nvarchar(256),
@PosID int,
@PageIndex int,
@PageSize int
)
AS
SET NOCOUNT ON

IF @HamTeamsID = 0 SET @HamTeamsID = NULL
IF @Name = '0' SET @Name = NULL
IF @Surname = '0' SET @Surname = NULL
IF @PosID = 0 SET @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

monfu

Posted: Monday, October 01, 2007 3:56:42 AM

Rank: Newbie

Joined: 9/28/2007
Posts: 3
Points: 9
Location: Dublin

I tried to change the order of the parameteres

WHERE (HS_Players.fk_hamTeamID = COALESCE (@HamTeamsID, HS_Players.fk_hamTeamID))

, however now its not returning any rows.

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

Scott Whigham

Posted: Monday, October 01, 2007 1:24:42 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

monfu wrote:

SELECT *
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))

Are you sure you don't have the COALESCE items switched? Your statement "COALESCE (HS_Players.fk_hamTeamID, @HamTeamsID)" says "If HS_Players.fk_hamTeamID, replace it with @HamTeamsID". Before that, though, you assign @HamsTeamId = NULL for certain values.

Other than that, I'm sorry but I don't have time this week to dig through the code Frown

monfu

Posted: Tuesday, October 02, 2007 4:19:00 AM

Rank: Newbie

Joined: 9/28/2007
Posts: 3
Points: 9
Location: Dublin

I found my error guys

this:-

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))

Should read like this:-

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_posID = COALESCE (HS_Players.fk_posID, @PosID))

Scott Whigham

Posted: Wednesday, October 03, 2007 1:02:22 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Very good - thanks for posting the solution.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.