CODEWITHSUNDEEP
sqlsql-serverif-statement
user2571510
user2571510

Reputation: 11377

SQL Server: how to use if else statement to select different data from same table

I am using the following stored procedure to select data from a table which works fine so far.

Now I want to create two different cases without duplicating the whole query: If the input @selection = "active" then select everything that DOES NOT equal statusX = "Published" else select everything that DOES equal statusX = "Published".

Can anyone tell me how I can achieve this here ?

My stored procedure (reduced columns): 

ALTER PROCEDURE [dbo].[FetchRequests]
    @selection nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT      col1,
                col2,
                col3,
                col4,
                col5,
                statusX,
                logStatus,
                logID
    FROM        LogRequests
    WHERE       logStatus = 'active'
    ORDER BY    logID desc
    FOR XML PATH('cols'), ELEMENTS, TYPE, ROOT('ranks')

END

Many thanks for any help with this, Tim.

Upvotes: 0

Views: 191

Answers (5)

PeterRing
PeterRing

Reputation: 1797

ALTER PROCEDURE [dbo].[FetchRequests]
    @selection nvarchar(20)
AS
BEGIN
Declare @query as nvarchar(4000)

@query=
    'SET NOCOUNT ON;
    SELECT      col1,
                col2,
                col3,
                col4,
                col5,
                statusX,
                logStatus,
                logID
    FROM        LogRequests
    WHERE       logStatus = ''active'' and statusX ' + 
    Select case when @selection = 'active' then '<>'
              else '='
    END + '''Published''
    ORDER BY    logID desc
    FOR XML PATH(''cols''), ELEMENTS, TYPE, ROOT(''ranks'')'
execute @query
END

Upvotes: 1

oerkelens
oerkelens

Reputation: 5161

Add an AND clause to your WHERE:

AND (@selection = 'active') = (StatusX <> 'Published')

(I see it's a combination of the two lines proposed by the other posters. Well, matter of taste I guess :) )

Upvotes: 2

Jonathan
Jonathan

Reputation: 1833

You can just use a conditional WHERE clause:

ALTER PROCEDURE [dbo].[FetchRequests]
    @selection nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT      col1,
                col2,
                col3,
                col4,
                col5,
                statusX,
                logStatus,
                logID
    FROM        LogRequests
    WHERE       logStatus = 'active'
    AND ((statusX <> 'Published' AND @selection = 'active')
    OR (statusX = 'Published' AND @selection <> 'active'))
    ORDER BY    logID desc
    FOR XML PATH('cols'), ELEMENTS, TYPE, ROOT('ranks')

END

Upvotes: 3

Adriaan Stander
Adriaan Stander

Reputation: 166476

You could try something like

SELECT      col1,
            col2,
            col3,
            col4,
            col5,
            statusX,
            logStatus,
            logID
FROM        LogRequests
WHERE       logStatus = 'active'
AND         (
                    (@selection = "active" AND statusX <> "Published")
                OR  (@selection <> "active" AND statusX = "Published")
            )
ORDER BY    logID desc
FOR XML PATH('cols'), ELEMENTS, TYPE, ROOT('ranks')

Upvotes: 1

Alex K.
Alex K.

Reputation: 175876

How about

where 
(@selection = 'active' and statusX != 'Published') 
or
(@selection != 'active' and statusX = 'Published')

Upvotes: 1

Related Questions