locked
Stored Procedure conditional WHERE RRS feed

  • Question

  • User-90830185 posted

    My SQL stored procedure is as follow:

    CREATE PROCEDURE [dbo].[GetUser_Pager]
    @PageIndex INT
    ,@PageSize INT
    ,@JmtID INT
    ,@UserType NVARCHAR(50)
    ,@Country NVARCHAR(100)
    ,@RecordCount INT OUTPUT
    AS
    BEGIN
    DECLARE @SQL NVARCHAR(MAX)

    SET NOCOUNT ON;

    SELECT ROW_NUMBER() OVER
    (
    ORDER BY [usrFirstName] ASC
    )AS RowNumber
    ,tabUsers.*
    ,jmtJamaat
    INTO #Results
    FROM tabUsers, tabJamaat
    WHERE usrJmtID = jmtID

    --****** Insertion Point *******

    SET @RecordCount = (SELECT COUNT(*) FROM #Results)

    SELECT * FROM #Results
    WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1;

    DROP TABLE #Results

    END

    The procedure works fine for a simple joint SELECT and you will notice that it is used for paging within the GridView.

    However, I am struggling to expand my WHERE clause with an AND operator at this location "--****** Insertion Point *******" above so that when a certain condition is met e.g.

    If @UserType = 'ADMIN' then I want to add an 'AND' to my WHERE clause as follows:

    WHERE usrJmtID = jmtID
    AND usrUserType = 'ADMIN'

    Would appreciate if someone can assist please?

    Sunday, April 10, 2016 8:44 PM

Answers

  • User-90830185 posted

    Sorry but I have resolved the issue by testing the condition on the client side and depending on the condition I am suing different stored procedure. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 14, 2016 3:52 PM

All replies

  • User372437940 posted

    Try:

    WHERE 
        usrJmtID = jmtID AND 
        (usrUserType = 'ADMIN' OR @UserType <> 'ADMIN')

    Hope this help

    Monday, April 11, 2016 12:28 AM
  • User364663285 posted

    Try

    WHERE (usrJmtID = jmtID
    AND usrUserType = 'ADMIN' or 1=1)

    Monday, April 11, 2016 1:34 AM
  • User-90830185 posted

    Sorry guys, may be I have not explained it properly.

    This what I have at the moment:

    WHERE usrJmtID = jmtID

    and now I want to add this:

    AND usrUserType = 'ADMIN'

    but before adding the the above line I want to first check:

    If @UserType = 'ADMIN'

    The problem is how do I add the "IF" statement ?

    So where is the code before:

    SELECT ROW_NUMBER() OVER
    (
    ORDER BY [usrFirstName] ASC
    )AS RowNumber
    ,tabUsers.*
    ,jmtJamaat
    INTO #Results
    FROM tabUsers, tabJamaat 
    WHERE usrJmtID = jmtID

    The code after:

    SELECT ROW_NUMBER() OVER
    (
    ORDER BY [usrFirstName] ASC
    )AS RowNumber
    ,tabUsers.*
    ,jmtJamaat
    INTO #Results
    FROM tabUsers, tabJamaat
    WHERE usrJmtID = jmtID

    If @UserType = 'ADMIN'
    BEGIN
    AND usrUserType = 'SOME OTHER HARD CODED VALUE'
    END

    Basically I want to append "AND" to the existing WHERE clause depending on the parameter value.

    Monday, April 11, 2016 7:44 PM
  • User372437940 posted

    Have you tried the way I suggested?

    WHERE 
        usrJmtID = jmtID AND 
        (usrUserType = 'SOME OTHER HARD CODED VALUE' OR @UserType <> 'ADMIN')

    Monday, April 11, 2016 8:31 PM
  • User-219423983 posted

    Hi Moodhi,

    Basically I want to append "AND" to the existing WHERE clause depending on the parameter value.

    I think you don’t need to grapple with how to append “AND” to where clause based on @UserType = 'ADMIN'. Because, it would return the same result as gapimex’s suggestion. So, I strongly suggest you’d better first try gapimex’s suggestion.

    If you still what to build the query string, I think could follow below demo to achieve your need. During coding, you should must create a temporary table that has the same structure with the final result. Then, you could build the dynamic query string in the if statement as below.

    DECLARE @Command  nvarchar(500)
    
    DECLARE @Id int 
    SET @Id = 2
    
    IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL 
    DROP TABLE #MyTestTable 
    
    create table #MyTestTable (val nvarchar(10))
    IF (@Id = 2) BEGIN  
        SET @Command = 'SELECT ''ABC'' AS Letters'
    END ELSE BEGIN 
        SET @Command = 'SELECT ''XYZ'' AS Letters INTO'
    END 
    
    insert into #MyTestTable execute( @Command)
    
    select * from #MyTestTable
    

    Best Regards,

    Weibo Zhang

    Thursday, April 14, 2016 3:28 AM
  • User-90830185 posted

    Sorry but I have resolved the issue by testing the condition on the client side and depending on the condition I am suing different stored procedure. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 14, 2016 3:52 PM