Advanced Search

SQl Select cluase value conversion

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.