Error in COALESCE Stored Proc - 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)

Error in COALESCE Stored Proc

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2007-09-28 : 08:58:47

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-09-28 : 10:05:48

switch around the order


 COALESCE (HS_Players.fk_hamTeamID, @HamTeamsID))
to
 COALESCE (@HamTeamsID, HS_Players.fk_hamTeamID))

on all of them.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

monfu
Yak Posting Veteran

81 Posts

Posted - 2007-10-01 : 04:50:45

Hi DonAtWork.

I tried that, 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

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-10-01 : 08:40:11

--First, make your parameters optional, and define defaults for them:

ALTER PROCEDURE dbo.HS_Players_GetPlayers_BY_Criteria
(
@HamTeamsID int = null,
@Name nvarchar(100) = null,
@Surname nvarchar(256) = null,
@PosID int = null,
@PageIndex int = 1,
@PageSize int = 100
)

Then check out this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90128

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources