How to use between operator with case statement in SQL Server 2005

How to use BETWEEN operator with CASE statem开发者_开发问答ent

@FromEmpAge
@ToempAge

select * from Emp
where EmpAge between
  case when ToempAge0 then @FromEmpAge and @ToempAge end

How to do this?

Not sure if it works - but if it did, you would have to make sure to properly finish your CASE statement first!

SELECT 
    (list of columns) 
FROM 
    dbo.Emp
WHERE
    EmpAge BETWEEN 
      CASE (some column)                     | this is the first value 
          WHEN ToEmpAge0 THEN @FromEmpAge    | for the BETWEEN ....
      END                                    | close the CASE
      AND @ToempAge                          | this is the second value

The CASE needs to have at least one WHEN....THEN.... and then an END to "close" then CASE - only after that, you can keep on going.....

But again: not sure if this will work, even with the "right" syntax.....

I'm not sure about what you mean with ToEmpAge0. Anyway, marc_s has answered your question on how to use CASE statement within a BETWEEN operator, that will work if you put a proper literal value after the WHEN. You can also use the CASE statement for the second operand of the BETWEEN operator.

I will assume that ToEmpAge is a column of INT:

SELECT 
    (list of columns) 
FROM 
    dbo.Emp
WHERE
    EmpAge BETWEEN 
       CASE ToEmpAge 
          WHEN 0 THEN @FromEmpAge
          WHEN 1 THEN @FromEmpAge+10 --or whatever
       END
    AND
       CASE ToEmpAge
          WHEN 0 THEN @ToEmpAge
          WHEN 1 THEN @ToEmpAge+5
       END

        SELECT *
        FROM   CTE
        WHERE   RN BETWEEN case @Excel when 0 then (@StartRow - cast(@NumberOfRows AS int)) else @NumberOfRows end
                   AND case @Excel when 0 then (@StartRow - 1) else @PageIndex end

    DECLARE @StartRow INT
    SET @StartRow = ( cast(@PageIndex as int) * cast(@NumberOfRows as int) ) + 1 ;


    WITH    CTE
              AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY CASE
                                                          WHEN @SortColumnName = 'ID'
                                                          AND @SortOrderBy = 'asc'
                                                          THEN sod.ID
                                                          END ASC, CASE
                                                          WHEN @SortColumnName = 'ID'
                                                          AND @SortOrderBy = 'desc'
                                                          THEN sod.ID
                                                          END DESC, CASE
                                                          WHEN @SortColumnName = 'MessageText'
                                                          AND @SortOrderBy = 'asc'
                                                          THEN MessageText
                                                          END ASC, CASE
                                                          WHEN @SortColumnName = 'MessageText'
                                                          AND @SortOrderBy = 'desc'
                                                          THEN MessageText
                                                          END DESC, CASE
                                                          WHEN @SortColumnName = 'TO'
                                                          AND @SortOrderBy = 'desc'
                                                          THEN [TO]
                                                          END DESC
                                                           ) AS RN ,
   [SendedDate]
  ,[UserID]
  ,sod.[ID]
  ,[SmsOutboxID]
  ,[MessageID]
  ,[FolderID]
  ,[From]
  ,[TO]
  ,[MessageText]
  ,[SendedType]
  ,[SendedStatus]
  ,[IsDelete]
  ,[NumberOfMessage]
  ,[MessageType]
  ,[PricesbySMS]
  ,[Sended]


   FROM SMS_Outbox so inner join SMS_OutboxDetails sod on so.ID = sod.SmsOutboxID where so.UserID = @UserID and sod.SendedType = @Type and sod.IsDelete = 0)

        SELECT *
        FROM   CTE
        WHERE   RN BETWEEN case @Excel when 0 then (@StartRow - cast(@NumberOfRows AS int)) else @NumberOfRows end
                   AND case @Excel when 0 then (@StartRow - 1) else @PageIndex end

Write a query that will display the employee's name with the first letter capitalized and all other letters lowercase and department no. For all employees whose second character of their name is between ‘A’ and ‘M’, Give each column an appropriate label. I tried something that did not work. My work code is:

SELECT INITCAP(ename), deptno 
FROM  emp
WHERE ename LIKE BETWEEN ('_A%' AND '_M%');