stored procedure for counting records from filtered search

I have a stored procedure for coun开发者_运维知识库ting the records of a filtered search (see underneath)

However, if I do a filtered search on the fields 'academiejaar' (academic year) and 'studierichting' (course), I get a wrong count.

This is my stored procedure:

@voornaam nvarchar(50) = null,
@naam varchar(50) = null,
@studierichting varchar(50) = null,
@academiejaar varchar(9) = null,
@gastinstelling nvarchar(100) = null,
@periode varchar(50) = null,
@recordCount int = NULL OUTPUT

AS

IF (@recordCount IS NOT NULL)
BEGIN
SET @recordCount = (SELECT
COUNT(*) FROM Studenten
INNER JOIN  Curriculum 
ON Studenten.id=Curriculum.studentid 
INNER JOIN erasmus 
ON Studenten.id=erasmus.studentid 
WHERE
([studenten].voornaam LIKE '%' + @voornaam + '%' OR @voornaam IS NULL)
    AND ([studenten].naam LIKE '%' + @naam + '%' OR @naam IS NULL)
    AND ([erasmus].academiejaar = @academiejaar OR @academiejaar IS NULL)
    AND ([erasmus].gastinstelling LIKE '%' + @gastinstelling + '%' OR       @gastinstelling IS NULL) 
    AND ([erasmus].periode = @periode OR @periode IS NULL)
    AND ([curriculum].studierichting_vorig LIKE '%' + @studierichting + '%' OR @studierichting IS NULL))

RETURN
END

The count I'm getting back from this stored procedure is much to high. I get a recordCount of 39, whilst it should be 8

Does anyone see a error in this sql query?

Thanks for your help,

Anthony Belgium

Instead of returning the record count, why don't you select the rows that are returned given the same parameters. Then you can see which rows are being returned that you don't expect - seeing the actual data will give a better clue as to why the count is different. Without knowing the parameters you're passing, the data in the table, and the 39 rows that are returned, nobody here is going to be able to give you an easy answer.

CREATE PROCEDURE GETData
(
    @voornaam nvarchar(50) = null,
    @naam varchar(50) = null,
    @studierichting varchar(50) = null,
    @academiejaar varchar(9) = null,
    @gastinstelling nvarchar(100) = null,
    @periode varchar(50) = null,
    @recordCount int = NULL OUTPUT
)
AS

IF (@recordCount IS NOT NULL)
BEGIN

        ;with CTE AS
        (
            SELECT Distinct Studenten.id as CountID         
            FROM Studenten 
            INNER JOIN  Curriculum  ON Studenten.id=Curriculum.studentid 
            INNER JOIN erasmus ON Studenten.id=erasmus.studentid 
            WHERE ([studenten].voornaam LIKE '%' + @voornaam + '%' OR @voornaam IS NULL)
            AND ([studenten].naam LIKE '%' + @naam + '%' OR @naam IS NULL)
            AND ([erasmus].academiejaar = @academiejaar OR @academiejaar IS NULL)
            AND ([erasmus].gastinstelling LIKE '%' + @gastinstelling + '%' OR @gastinstelling IS NULL) 
            AND ([erasmus].periode = @periode OR @periode IS NULL)
            AND ([curriculum].studierichting_vorig LIKE '%' + @studierichting + '%' OR @studierichting IS NULL)
        )
        SELECT @recordCoun=COUNT(CountID)
        From CTE 

RETURN
END

'the great mystery' is solved. Seemed that the problem was not located at the stored procedure, but with my webapplication code, where the value from my dropdownlist was not past to the count() function in my code:

private int CountDossiers()
        {
            var studentCriteria = new StudentCriteria
                               {
                                   Voornaam = txtSearchVoornaam.Text,
                                   Naam = txtSearchNaam.Text
                               };

            if (!string.IsNullOrEmpty(ddlSearchStudieRichting.SelectedValue))
            {
                studentCriteria.Studierichting = (Studierichting)Enum.Parse(typeof(Studierichting), ddlSearchStudieRichting.SelectedValue);
            }
            **if (!string.IsNullOrEmpty(ddlSearchAcademieJaar.SelectedValue))
            {
                studentCriteria.Academiejaar = ddlSearchAcademieJaar.SelectedValue;
            }**
            return StudentManager.SelectCountForGetList(studentCriteria);

        }

That's why I got the wrong record count alle the time, the records from all academic years where counted, instead of the academic year entered in the search field.

Right now I feel a bit silly :)

Anthony