?
Solved

t-sql query

Posted on 2014-01-09
1
Medium Priority
?
302 Views
Last Modified: 2014-01-09
I'm using sql server 2008 R2

I have a test table called TestEmployeeHistory

The table looks like this:

my table
This is the script to create the table.

CREATE TABLE [dbo].[TestEmployeeHistory](
	[Region] [smallint] NULL,
	[SocialNo] [char](9) NULL,
	[EmployeePaidDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TestEmployeeHistory] ([Region], [SocialNo], [EmployeePaidDate]) VALUES (1, N'12345678 ', CAST(0x8E1302D0 AS SmallDateTime))
INSERT [dbo].[TestEmployeeHistory] ([Region], [SocialNo], [EmployeePaidDate]) VALUES (1, N'12345679 ', CAST(0x8E1302D0 AS SmallDateTime))
INSERT [dbo].[TestEmployeeHistory] ([Region], [SocialNo], [EmployeePaidDate]) VALUES (1, N'12345680 ', CAST(0x8E1302D0 AS SmallDateTime))
INSERT [dbo].[TestEmployeeHistory] ([Region], [SocialNo], [EmployeePaidDate]) VALUES (1, N'12345681 ', CAST(0x8E1302D0 AS SmallDateTime))
INSERT [dbo].[TestEmployeeHistory] ([Region], [SocialNo], [EmployeePaidDate]) VALUES (1, N'12345682 ', CAST(0x912B02D0 AS SmallDateTime))

Open in new window


I have a query that looks like this:

DECLARE @Region varchar(800), @FromDate char(30), @ToDate char(30), @EmployeeSocial char(9)
SET @Region = '1'
SET @FromDate = '07/01/1999'
SET @ToDate = '06/30/2000'
SET @EmployeeSocial = NULL

SELECT h.Region
      ,h.SocialNo
      ,h.EmployeePaidDate
FROM  [TestDatabase].[dbo].[TestEmployeeHistory] h
WHERE
           IF (@Region <> '0')
             BEGIN
	             h.Region IN (RTrim(@Region)) AND 
             END
           If (@EmployeeSocial <> '')
             BEGIN
	             h.SocialNo = @EmployeeSocial AND
             END

GROUP BY h.Region
        ,h.SocialNo
        ,h.EmployeePaidDate

Open in new window


When I run this query I get this error message.

my error message
It seems I am making a mistake in the syntax of my if statements

Anyone where I'm making a mistake in my if statements?
0
Comment
Question by:maqskywalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39769605
You can't use "IF" within a query.  You need to use "CASE" instead, or just write the WHERE conditions to do what you need.


WHERE
    ( @Region = 0 OR h.Region IN (RTrim(@Region)) ) AND
    ( @EmployeeSocial = '' OR h.SocialNo = @EmployeeSocial )
GROUP BY h.Region
        ,h.SocialNo
        ,h.EmployeePaidDate
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

765 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