good day,

i have a table named information
i created a stored procedure that will search for thier name, "firstname","middlename" and "lastname"

that 3 field is concatenated and named it fullname

My problem is that i want to count the result of my query that count the total number of return based on my condition. and if the result does not return a value, the message should be "RECORD NOT FOUND!"

here's my sample query for search

SELECT
            MRN
            ,CONVERT(DATE,BIRTHDATE)BIRTHDATE
            ,FIRSTNAME +' '+MIDDLENAME+' ' +LASTNAME as FULLNAME
            ,GENDER
            ,MARITAL_STATUS
            ,RELIGION
            ,OCCUPATION 
            ,ADDRESS1
    FROM [dbHIS].[dbo].[PATIENT_INFORMATION]


    WHERE (FIRSTNAME LIKE @FIRSTNAME OR @FIRSTNAME IS NULL)
            OR (LASTNAME LIKE @LASTNAME OR @LASTNAME IS NULL)
            OR (MIDDLENAME LIKE @MIDDLENAME OR @MIDDLENAME IS NULL) 

hope that some will help me.. thanks

I didn't understand much of what you want... but to get the count it would be:

DECLARE @rowsCount int

SELECT
    @rowsCount = count(*)
FROM 
    [dbHIS].[dbo].[PATIENT_INFORMATION]
WHERE
    (FIRSTNAME LIKE @FIRSTNAME OR @FIRSTNAME IS NULL)
    OR (LASTNAME LIKE @LASTNAME OR @LASTNAME IS NULL)
    OR (MIDDLENAME LIKE @MIDDLENAME OR @MIDDLENAME IS NULL) 

Good day,

All i want is to get the total number of result based on my query. i want to display all the result in my gridview, if there's no result return i can make a decision to register the patient.

i'll try your codes later..

GOOD BLESS.
thank you for your reply.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.