Posted By

kashif21 on 04/01/11


Tagged

select sql Value conversion cluase


Versions (?)

SQl Select cluase value conversion


 / Published in: SQL
 

  1. SELECT
  2. AuditID,
  3. LoginName,
  4. LoginDatetime,
  5. IPAddress,
  6. CASE IsAuthenticated
  7. WHEN 1 THEN 'Yes'
  8. ELSE 'No'
  9. END AS IsAuthenticated,
  10. ROW_NUMBER()
  11. OVER (ORDER BY
  12. CASE WHEN @strSortCol = 'AuditID ASC' THEN AuditID END,
  13. CASE WHEN @strSortCol = 'AuditID DESC' THEN AuditID END DESC,
  14. CASE WHEN @strSortCol = 'LoginName ASC' THEN LoginName END,
  15. CASE WHEN @strSortCol = 'LoginName DESC' THEN LoginName END DESC,
  16. CASE WHEN @strSortCol = 'LoginDatetime ASC' THEN LoginDatetime END,
  17. CASE WHEN @strSortCol = 'LoginDatetime DESC' THEN LoginDatetime END DESC,
  18. CASE WHEN @strSortCol = 'IPAddress ASC' THEN IPAddress END,
  19. CASE WHEN @strSortCol = 'IPAddress DESC' THEN IPAddress END DESC,
  20. CASE WHEN @strSortCol = 'IsAuthenticated ASC' THEN IsAuthenticated END,
  21. CASE WHEN @strSortCol = 'IsAuthenticated DESC' THEN IsAuthenticated END DESC
  22.  
  23. ) AS RowNum,
  24. COUNT(*) OVER() AS [TotalRows]
  25. FROM
  26. AuditLogin
  27. WHERE
  28. (@strUserName = '' OR LoginName LIKE '%' + @strUserName + '%')
  29. AND LoginDateTime >= @dtmFromDate AND LoginDateTime <= @dtmToDate
  30. )

Report this snippet  

You need to login to post a comment.