Error in Stored Procedure

  • 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

  • You need to reverse the order in your coalesce's..

    You have (column, variable that my be null)

    You should have (variable, column)

    /Kenneth

  • 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.

  • 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

  • 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

  • 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.

  • 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.

  • 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

  • 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.

  • 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