Solved

t-sql query

Posted on 2014-01-23
2
105 Views
Last Modified: 2014-01-25
Hi experts,

I'm using sql server 2008 R2.

I have a query where in the WHERE clause I have the following if statements.

SELECT * 
FROM MyTaBLE h, MyOtherTable d
WHERE
          If (@Region <> '0')
              begin
                     h.Region in (RTrim(@Region)) AND
              end
          If (@EmployeeSocial <> '')
              begin
                     h.EmployeeSocial = @EmployeeSocial AND
            end
      h.Region = d.Region

Open in new window


Is there a better way to write the if statements that are in my WHERE clause without using if statements?

FYI, my parameters are of these datatypes:

@Region varchar(800), @EmployeeSocial char(9)
0
Comment
Question by:maqskywalker
  • 2
2 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39805366
Something like this perhaps:
SELECT  *
FROM    MyTaBLE h
        INNER JOIN MyOtherTable d ON h.Region = d.Region
WHERE   (@Region <> '0' AND h.Region = RTRIM(@Region))
        AND (@EmployeeSocial <> '' AND h.EmployeeSocial = @EmployeeSocial)

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39809488
Incidentally you should not need to do a RTRIM() .  Typically the following are always true:
'Anthony' = 'Anthony       '

If you are bound and determined to use RTIM() than do this:
SET @Region = RTRIM(@Region)
SELECT  *
FROM    MyTaBLE h
        INNER JOIN MyOtherTable d ON h.Region = d.Region
WHERE   (@Region <> '0' AND h.Region = @Region)
        AND (@EmployeeSocial <> '' AND h.EmployeeSocial = @EmployeeSocial)

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question