pass Where Clause as a parameter to the stored procedure

Question

i have stored procedure for paging and i hava paramter @where i need to pass it to statment my SP as follow

    Create PROCEDURE SP_hrm_Employee_Paged
(
       @PageNo INT = 1,
       @PageSize INT = 10,
       @where nvarchar(500)

)
AS
BEGIN
         /*–Declaring Local Variables corresponding to parameters for modification */
         DECLARE 

         @lPageNbr INT,
         @lPageSize INT,
         @lFirstRec INT,
         @lLastRec INT,
         @lTotalRows INT
        /*Setting Local Variables*/
        SET @lPageNbr = @PageNo
        SET @lPageSize = @PageSize
         SET @lFirstRec = ( @lPageNbr - 1 ) * @lPageSize
         SET @lLastRec = ( @lPageNbr * @lPageSize + 1 )
         SET @lTotalRows = @lFirstRec - @lLastRec + 1
         ; WITH CTE_Results
         AS (
         SELECT ROW_NUMBER() over(ORDER By BranchId ASC) as ROWNUM,
       Count(*) over () AS TotalCount,
        EmployeeId,
       EmployeeFirstName,
    EmployeeMiddleName,
    EmployeeLastName,
    EmployeeMobile,
    EmployeeMobile2,
    EmployeeEmail,
    EmployeeAddress
   FROM t_hrm_employees


)

SELECT
    TotalCount,
    ROWNUM,
          EmployeeId,
       EmployeeFirstName,
    EmployeeMiddleName,
    EmployeeLastName,
    EmployeeMobile,
    EmployeeMobile2,
    EmployeeEmail,
    EmployeeAddress

FROM CTE_Results AS CPC
WHERE 
         ROWNUM > @lFirstRec
               AND ROWNUM < @lLastRec 
 ORDER BY ROWNUM ASC
END

look into this http://stackoverflow.com/questions/31985175/how-to-execute-sub-query-in-if-exists-condition/31985628#31985628 — Raj Kamuni, Sep 16 '15 at 09:52
Feels like a code smell that some external code has to have intimate knowledge of the query inside this stored proc in order that it can write a sensible where clause that will work with it. Maybe read [Dynamic Search Conditions in T‑SQL](http://www.sommarskog.se/dyn-search.html). Also, as a side note, avoid the `sp_` prefix - it's reserved for Microsoft's **system** procedures. — Damien_The_Unbeliever, Sep 16 '15 at 09:58

Krishnraj Rana · Answer 1 · 2015-09-16T10:34:09.937

you can use dynamic sql using SP_EXECUTESQL like this -

But before please note that -- don't start your SP name with "sp_" prefix because its reserved for system stored procedure in sql server.

CREATE PROCEDURE usp_hrm_Employee_Paged (
    @PageNo INT = 1
    ,@PageSize INT = 10
    ,@where NVARCHAR(500)
    )
AS
BEGIN /*–Declaring Local Variables corresponding to parameters for modification */
    DECLARE @lPageNbr INT
        ,@lPageSize INT
        ,@lFirstRec INT
        ,@lLastRec INT
        ,@lTotalRows INT

    /*Setting Local Variables*/
    SET @lPageNbr = @PageNo
    SET @lPageSize = @PageSize
    SET @lFirstRec = (@lPageNbr - 1) * @lPageSize
    SET @lLastRec = (@lPageNbr * @lPageSize + 1)
    SET @lTotalRows = @lFirstRec - @lLastRec + 1;

    DECLARE @ExequteSql NVARCHAR(MAX)

    SET @ExequteSql = N'
    WITH CTE_Results
    AS (
        SELECT ROW_NUMBER() OVER (
                ORDER BY BranchId ASC
                ) AS ROWNUM
            ,Count(*) OVER () AS TotalCount
            ,EmployeeId
            ,EmployeeFirstName
            ,EmployeeMiddleName
            ,EmployeeLastName
            ,EmployeeMobile
            ,EmployeeMobile2
            ,EmployeeEmail
            ,EmployeeAddress
        FROM t_hrm_employees
        WHERE ' + @where + '
        )
    SELECT TotalCount
        ,ROWNUM
        ,EmployeeId
        ,EmployeeFirstName
        ,EmployeeMiddleName
        ,EmployeeLastName
        ,EmployeeMobile
        ,EmployeeMobile2
        ,EmployeeEmail
        ,EmployeeAddress
    FROM CTE_Results AS CPC
    WHERE ROWNUM > @lFirstRec
        AND ROWNUM < @lLastRec
    ORDER BY ROWNUM ASC'

    EXEC SP_EXECUTESQL @ExequteSql ,N'@lFirstRec INT, @lLastRec INT' ,@lFirstRec = @lFirstRec ,@lLastRec = @lLastRec

END

where i pass the parameter @where? – salah Sep 16 '15 at 09:57 — salah, Sep 16 '15 at 09:57

pass Where Clause as a parameter to the stored procedure

1 Answers1