Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error in Stored Procedure Expand / Collapse
Author
Message
Posted Friday, September 28, 2007 7:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 6:45 AM
Points: 151, Visits: 107
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
Post #404004
Posted Friday, September 28, 2007 7:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 09, 2014 3:33 AM
Points: 1,559, Visits: 672
You need to reverse the order in your coalesce's..

You have (column, variable that my be null)
You should have (variable, column)

/Kenneth



Post #404016
Posted Friday, September 28, 2007 7:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 6:45 AM
Points: 151, Visits: 107
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.
Post #404027
Posted Monday, October 01, 2007 1:56 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 09, 2014 3:33 AM
Points: 1,559, Visits: 672
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



Post #404884
Posted Monday, October 01, 2007 2:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 6:45 AM
Points: 151, Visits: 107
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
Post #404900
Posted Monday, October 01, 2007 3:52 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 4:24 AM
Points: 685, Visits: 104
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)



Everything you can imagine is real.

Post #404917
Posted Monday, October 01, 2007 4:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 6:45 AM
Points: 151, Visits: 107
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
(
@HamTeamsID int,
@Name nvarchar(100),
@Surname nvarchar(256),
@PosID int,
@PageIndex int,
@PageSize int
)
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.
Post #404931
Posted Monday, October 01, 2007 4:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 6:45 AM
Points: 151, Visits: 107
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
Post #404933
Posted Monday, October 01, 2007 5:01 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 4:24 AM
Points: 685, Visits: 104
Johann Montfort (10/1/2007)
Hi Kenneth

I found my error.
...
I was repeating the same variable @HamTeamsID, when it was supposed to be @PosID!

Johann


so what worked?



Everything you can imagine is real.

Post #404946
Posted Monday, October 01, 2007 5:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 6:45 AM
Points: 151, Visits: 107
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
Post #404949
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse