How to count results of stored procedure and show only 100 rows

Question

I wrote a stored procedure to search the users in my database. But sometimes it returns too many users, and it takes too much time.

So if the result set has over 500 rows, I decided to return only the first 500 rows. But I have no idea how to get count from stored procedure.

Please help me.

My original stored procedure is below. Thanks

ALTER PROCEDURE [dbo].[up_Select_SearchUser]
    @companyCode    varchar(50)
,   @keyword        nvarchar(20)
,   @isRelative     bit
,   @langCode       varchar(10)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT     
        LOWER(M.UserID) AS EmpID,
        LOWER(M.EmpID) AS REmpID,
        dbo.uf_CompanyPhone(M.CompanyPhone, M.ExtensionNumber) AS ExtensionNumber,  
        M.CellPhone,
        dbo.uf_TeamManagerYN(M.UserID) AS TeamChiefYN, 
        ISNULL(M.DisplayName, '') AS DisplayName, 
        ISNULL(M.DisplayName_Eng, '') AS DisplayName_Eng,
        M.EMail, 
        M.MainDeptCode AS DeptCode,
        ISNULL(DT.DisplayName, '') AS DeptName,
        ISNULL(CL.CompanyCode, '') AS CompanyCode,
        ISNULL(CL.CompanyName, '') AS CompanyName,
        ISNULL(R.RankCode, '') AS RankCode,
        ISNULL(R.RankName, '') AS RankName, 
        ISNULL(J.JobCode, '') AS JobCode,
        ISNULL(J.JobName, '') AS JobName,
        ISNULL(D.DutyCode, '') AS DutyCode,
        ISNULL(D.DutyName, '') AS DutyName,
        ISNULL(M.FaxNumber, '') AS FaxNumber,
        ISNULL(M.ADDisplayName, '') AS ADDisplayName,
        '' AS Address,
        ISNULL(M.CompanyPhone, '') AS CompanyPhone, 
        ISNULL(R.SortOrder, '') AS RankOrder
    FROM     
        dbo.tb_User M WITH (NOLOCK) 
    INNER JOIN 
        dbo.tb_Dept DT WITH (NOLOCK) ON M.MainDeptCode = DT.DeptCode 
    INNER JOIN 
        dbo.tb_Rank R WITH (NOLOCK) ON M.RankCode = R.RankCode
                                    AND M.GroupCode = R.GroupCode
                                    AND M.DetailCode = R.DetailCode
                                    AND M.CompanyCode = R.CompanyCode
    INNER JOIN 
        dbo.tb_Company CL WITH (NOLOCK) ON M.CompanyCode = CL.CompanyCode
    LEFT OUTER JOIN 
        dbo.tb_Duty D WITH (NOLOCK) ON M.DutyCode = D.DutyCode 
                                    AND M.GroupCode = D.GroupCode
                                    AND M.DetailCode = D.DetailCode
                                    AND M.CompanyCode = D.CompanyCode
    LEFT OUTER JOIN 
        dbo.tb_Job J WITH (NOLOCK) ON M.JobCode = J.JobCode 
    WHERE    
        (M.DisplayName LIKE '%' + @keyword + '%' 
         OR M.UserID LIKE '%' + @keyword + '%') 
        AND M.DisplayYN = 'Y' 
        AND M.CompanyCode LIKE @companyCode
    ORDER BY 
        RankOrder, JobCode, DisplayName
END

there must be thousands of examples of doing that out there....pls try searching. Also: schema, indexes, S-argable — Mitch Wheat, May 10 '17 at 06:01
You should look into your joins, if you are digging with the duplicate records. — Sankar, May 10 '17 at 06:12
Why dont you try Pagination with OFFSET / FETCH we can show 100 rows per page or more — , May 10 '17 at 07:21
yes,Dig into your join to find why you are getting many records.Paging and only paging you are looking for. — KumarHarsh, May 10 '17 at 09:28

score 0 · Answer 1 · edited May 10 '17 at 06:29

0

Select @Count = COUNT() 
From Table 
Where <Your Condition>

Then use IF statement:

if @Count > 500 then 
    SELECT TOP 500 
        LOWER(M.UserID) = AS EmpID

edited May 10 '17 at 06:29

marc_s

732,580
175
1,330
1,459

answered May 10 '17 at 06:04

Aldrin B.

1
3

score 0 · Answer 2 · answered May 10 '17 at 06:04

Add TOP 500 to the select query. This will make sure that the result count will not be more than 500. If it's less than 500 it will take the number of records.If it's greater than 500, it will filter the top 500 records.

ALTER PROCEDURE [dbo].[up_Select_SearchUser]
    @companyCode    varchar(50)
,   @keyword        nvarchar(20)
,   @isRelative     bit
,   @langCode       varchar(10)
AS
BEGIN
    SET NOCOUNT ON;

SELECT  TOP 500   LOWER(M.UserID)                                            AS EmpID 
         , LOWER(M.EmpID)                                             AS REmpID 
         , dbo.uf_CompanyPhone(M.CompanyPhone, M.ExtensionNumber)     AS ExtensionNumber 
         , M.CellPhone 
         , dbo.uf_TeamManagerYN(M.UserID)                             AS TeamChiefYN 
         , ISNULL(M.DisplayName,'')                                   AS DisplayName 
         , ISNULL(M.DisplayName_Eng,'')                               AS DisplayName_Eng 
         , M.EMail 
         , M.MainDeptCode                                             AS DeptCode 
         , ISNULL(DT.DisplayName,'')                                  AS DeptName 
         , ISNULL(CL.CompanyCode,'')                                  AS CompanyCode 
         , ISNULL(CL.CompanyName,'')                                  AS CompanyName 
         , ISNULL(R.RankCode,'')                                      AS RankCode 
         , ISNULL(R.RankName,'')                                      AS RankName 
         , ISNULL(J.JobCode,'')                                       AS JobCode 
         , ISNULL(J.JobName,'')                                       AS JobName 
         , ISNULL(D.DutyCode,'')                                      AS DutyCode 
         , ISNULL(D.DutyName,'')                                      AS DutyName 
         , ISNULL(M.FaxNumber,'')                                     AS FaxNumber 
         , ISNULL(M.ADDisplayName,'')                                 AS ADDisplayName 
         , ''                                                         AS Address
         , ISNULL(M.CompanyPhone, '')                                 AS CompanyPhone
         , ISNULL(R.SortOrder,'')                                     AS RankOrder
FROM     dbo.tb_User M WITH (NOLOCK) 
         INNER JOIN dbo.tb_Dept DT WITH (NOLOCK) 
            ON M.MainDeptCode = DT.DeptCode 
         INNER JOIN dbo.tb_Rank R WITH (NOLOCK) 
            ON M.RankCode = R.RankCode
            AND M.GroupCode = R.GroupCode
            AND M.DetailCode = R.DetailCode
            AND M.CompanyCode = R.CompanyCode
         INNER JOIN dbo.tb_Company CL WITH (NOLOCK) 
            ON M.CompanyCode = CL.CompanyCode             
         LEFT OUTER JOIN dbo.tb_Duty D WITH (NOLOCK) 
            ON M.DutyCode = D.DutyCode 
            AND M.GroupCode = D.GroupCode
            AND M.DetailCode = D.DetailCode
            AND M.CompanyCode = D.CompanyCode
         LEFT OUTER JOIN dbo.tb_Job J WITH (NOLOCK) 
            ON M.JobCode = J.JobCode 
WHERE    (M.DisplayName LIKE '%' + @keyword + '%' 
           OR M.UserID LIKE '%' + @keyword + '%') 
         AND M.DisplayYN = 'Y' 
         AND M.CompanyCode LIKE @companyCode
ORDER BY RankOrder , JobCode, DisplayName

Are you sure about execution time? – Sankar May 10 '17 at 06:10 — Sankar, May 10 '17 at 06:10

score 0 · Answer 3 · edited May 23 '17 at 12:26

0

Use this statement instead of SELECT TOP:

SELECT * FROM Products
WHERE id BETWEEN 10 AND 20;

for a better control

for a better example wiev: Equivalent of LIMIT and OFFSET for SQL Server?

edited May 23 '17 at 12:26

Community

1
1

answered May 10 '17 at 06:05

NoOorZ24

2,914
1
14
33

this is just an example but it can be modified to fit your query – NoOorZ24 May 10 '17 at 06:07
What if their id starts from 1000? – Sankar May 10 '17 at 06:08
@SankarRaj well this works for DB's where ID's aren't deleted and you can use COUNT to get max value and work with it – NoOorZ24 May 10 '17 at 06:11

score 0 · Answer 4 · answered May 10 '17 at 06:27

As i understand from your question is you wants to select only 500 or 100 records its upto u how many records u wants to select simply u can use

select top 500 [lisofyourcolums] from your table;

if u want do a paging u can simply do changes in your Stored procedure like below

ALTER PROCEDURE [dbo].[up_Select_SearchUser]
    @companyCode    varchar(50)
,   @keyword        nvarchar(20)
,   @isRelative     bit
,   @langCode       varchar(10)
,   @startRowIndex int=1
,   @maximumRows int=500
AS
BEGIN
SET NOCOUNT ON;
      DECLARE  @startRow int,@totalRows int
    SET @startRowIndex =  (@startRowIndex - 1)  * @maximumRows
    Print @startRowIndex

    IF @startRowIndex = 0 
     SET @startRowIndex = 1
    Else
     SET @startRowIndex = @startRowIndex+1
    Print @startRowIndex
    SET ROWCOUNT @startRowIndex  


    select [yourcolumnList] from yourtable 

    SET ROWCOUNT @maximumRows 


SELECT  

END

I hope this will help u...

score 0 · Answer 5 · answered May 10 '17 at 07:29

Try this

ALTER PROCEDURE [dbo].[up_Select_SearchUser]
        @companyCode    varchar(50)
    ,   @keyword        nvarchar(20)
    ,   @isRelative     bit
    ,   @langCode       varchar(10)
    ,   @PageNumber INT = 1--Give Page number you required 
    ,   @PageSize   INT = 100 --Give number of rows you required per Page
    AS
    BEGIN
        SET NOCOUNT ON;


    SELECT     LOWER(M.UserID)                                            AS EmpID 
             , LOWER(M.EmpID)                                             AS REmpID 
             , dbo.uf_CompanyPhone(M.CompanyPhone, M.ExtensionNumber)     AS ExtensionNumber 
             , M.CellPhone 
             , dbo.uf_TeamManagerYN(M.UserID)                             AS TeamChiefYN 
             , ISNULL(M.DisplayName,'')                                   AS DisplayName 
             , ISNULL(M.DisplayName_Eng,'')                               AS DisplayName_Eng 
             , M.EMail 
             , M.MainDeptCode                                             AS DeptCode 
             , ISNULL(DT.DisplayName,'')                                  AS DeptName 
             , ISNULL(CL.CompanyCode,'')                                  AS CompanyCode 
             , ISNULL(CL.CompanyName,'')                                  AS CompanyName 
             , ISNULL(R.RankCode,'')                                      AS RankCode 
             , ISNULL(R.RankName,'')                                      AS RankName 
             , ISNULL(J.JobCode,'')                                       AS JobCode 
             , ISNULL(J.JobName,'')                                       AS JobName 
             , ISNULL(D.DutyCode,'')                                      AS DutyCode 
             , ISNULL(D.DutyName,'')                                      AS DutyName 
             , ISNULL(M.FaxNumber,'')                                     AS FaxNumber 
             , ISNULL(M.ADDisplayName,'')                                 AS ADDisplayName 
             , ''                                                         AS Address
             , ISNULL(M.CompanyPhone, '')                                 AS CompanyPhone
             , ISNULL(R.SortOrder,'')                                     AS RankOrder
    FROM     dbo.tb_User M WITH (NOLOCK) 
             INNER JOIN dbo.tb_Dept DT WITH (NOLOCK) 
                ON M.MainDeptCode = DT.DeptCode 
             INNER JOIN dbo.tb_Rank R WITH (NOLOCK) 
                ON M.RankCode = R.RankCode
                AND M.GroupCode = R.GroupCode
                AND M.DetailCode = R.DetailCode
                AND M.CompanyCode = R.CompanyCode
             INNER JOIN dbo.tb_Company CL WITH (NOLOCK) 
                ON M.CompanyCode = CL.CompanyCode             
             LEFT OUTER JOIN dbo.tb_Duty D WITH (NOLOCK) 
                ON M.DutyCode = D.DutyCode 
                AND M.GroupCode = D.GroupCode
                AND M.DetailCode = D.DetailCode
                AND M.CompanyCode = D.CompanyCode
             LEFT OUTER JOIN dbo.tb_Job J WITH (NOLOCK) 
                ON M.JobCode = J.JobCode 
    WHERE    (M.DisplayName LIKE '%' + @keyword + '%' 
               OR M.UserID LIKE '%' + @keyword + '%') 
             AND M.DisplayYN = 'Y' 
             AND M.CompanyCode LIKE @companyCode
    ORDER BY RankOrder , JobCode, DisplayName

        OFFSET @PageSize * (@PageNumber - 1) ROWS
        FETCH NEXT @PageSize ROWS ONLY 


    END

score 0 · Answer 6 · answered May 10 '17 at 08:44

Declare one variable to get input from user to show how many records he wants to return

ALTER PROCEDURE [dbo].[up_Select_SearchUser]
    @companyCode    varchar(50)
,   @keyword        nvarchar(20)
,   @isRelative     bit
,   @langCode       varchar(10)
,   @NumberOfRows INT
AS
BEGIN
    SET NOCOUNT ON;

SELECT TOP @NumberOfRows 
    LOWER(M.UserID) AS EmpID,
    LOWER(M.EmpID) AS REmpID,
    dbo.uf_CompanyPhone(M.CompanyPhone, M.ExtensionNumber) AS ExtensionNumber,  
    M.CellPhone,
    dbo.uf_TeamManagerYN(M.UserID) AS TeamChiefYN, 
    ISNULL(M.DisplayName, '') AS DisplayName, 
    ISNULL(M.DisplayName_Eng, '') AS DisplayName_Eng,
    M.EMail, 
    M.MainDeptCode AS DeptCode,
    ISNULL(DT.DisplayName, '') AS DeptName,
    ISNULL(CL.CompanyCode, '') AS CompanyCode,
    ISNULL(CL.CompanyName, '') AS CompanyName,
    ISNULL(R.RankCode, '') AS RankCode,
    ISNULL(R.RankName, '') AS RankName, 
    ISNULL(J.JobCode, '') AS JobCode,
    ISNULL(J.JobName, '') AS JobName,
    ISNULL(D.DutyCode, '') AS DutyCode,
    ISNULL(D.DutyName, '') AS DutyName,
    ISNULL(M.FaxNumber, '') AS FaxNumber,
    ISNULL(M.ADDisplayName, '') AS ADDisplayName,
    '' AS Address,
    ISNULL(M.CompanyPhone, '') AS CompanyPhone, 
    ISNULL(R.SortOrder, '') AS RankOrder
FROM     
    dbo.tb_User M WITH (NOLOCK) 
INNER JOIN 
    dbo.tb_Dept DT WITH (NOLOCK) ON M.MainDeptCode = DT.DeptCode 
INNER JOIN 
    dbo.tb_Rank R WITH (NOLOCK) ON M.RankCode = R.RankCode
                                AND M.GroupCode = R.GroupCode
                                AND M.DetailCode = R.DetailCode
                                AND M.CompanyCode = R.CompanyCode
INNER JOIN 
    dbo.tb_Company CL WITH (NOLOCK) ON M.CompanyCode = CL.CompanyCode
LEFT OUTER JOIN 
    dbo.tb_Duty D WITH (NOLOCK) ON M.DutyCode = D.DutyCode 
                                AND M.GroupCode = D.GroupCode
                                AND M.DetailCode = D.DetailCode
                                AND M.CompanyCode = D.CompanyCode
LEFT OUTER JOIN 
    dbo.tb_Job J WITH (NOLOCK) ON M.JobCode = J.JobCode 
WHERE    
    (M.DisplayName LIKE '%' + @keyword + '%' 
     OR M.UserID LIKE '%' + @keyword + '%') 
    AND M.DisplayYN = 'Y' 
    AND M.CompanyCode LIKE @companyCode
ORDER BY 
    RankOrder, JobCode, DisplayName
END

How to count results of stored procedure and show only 100 rows

6 Answers6