Solved

Incorrect syntax near the keyword 'BETWEEN'

Posted on 2012-03-27
3
1,164 Views
Last Modified: 2012-03-27
I could not figure out what is wrong with my syntax:

declare @StartDate DateTime,
@EndDate DateTime,
@BranchName varchar (20),
@ReportType varchar(50)
set @StartDate='02/1/2012'
set @EndDate='02/29/2012'

SELECT [Branch Name], [Stage Number],
[Not Signed Application Date],
[Fund Date],
[UW Approve Date]
FROM          tablename
WHERE        
 [Branch Name] in(@BranchName)AND ([Stage Number] >= 20)  AND
case
 WHEN @ReportType=a'
    THEN [Not Signed Application Date] BETWEEN @StartDate AND @EndDate
WHEN @ReportType='b'
  THEN [Fund Date] BETWEEN @StartDate AND @EndDate
WHEN @ReportType='c'
   THEN [UW Approve Date] BETWEEN @StartDate AND @EndDate
END


Thanks!
0
Comment
Question by:sharon2011
[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
  • 2
3 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37771492
The THEN and ELSE results must be a single *value* (or an expression that ultimately yields a single value), NOT a keyword or other multi-word result.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37771501
Therefore, change it to this:


case
 WHEN @ReportType=a' AND [Not Signed Application Date] BETWEEN @StartDate AND @EndDate THEN 1
WHEN @ReportType='b' AND [Fund Date] BETWEEN @StartDate AND @EndDate THEN 1
WHEN @ReportType='c' AND [UW Approve Date] BETWEEN @StartDate AND @EndDate THEN 1
ELSE 0
END = 1
0
 

Author Closing Comment

by:sharon2011
ID: 37771583
It works. Thank you very very much.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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