SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error in Stored Procedure


Error in Stored Procedure

Author
Message
Johann Montfort
Johann Montfort
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 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
Kenneth Wilhelmsson
Kenneth Wilhelmsson
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3561 Visits: 699
You need to reverse the order in your coalesce's..



You have (column, variable that my be null)

You should have (variable, column)



/Kenneth



Johann Montfort
Johann Montfort
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 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.
Kenneth Wilhelmsson
Kenneth Wilhelmsson
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3561 Visits: 699
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



Johann Montfort
Johann Montfort
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 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
bledu
bledu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1617 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.

Johann Montfort
Johann Montfort
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 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.
Johann Montfort
Johann Montfort
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 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
bledu
bledu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1617 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.

Johann Montfort
Johann Montfort
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search