Guest User

Untitled

a guest
Jul 17th, 2018
71
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.80 KB | None | 0 0
  1. WITH
  2. alias AS (...)
  3. IF @TopCount>0
  4. SELECT TOP (@TopCount) * FROM alias;
  5. ELSE
  6. SELECT * FROM alias;
  7. GO
  8.  
  9. CREATE PROC dbo.if_branch (@top INT = 2147483647)
  10. AS
  11. BEGIN
  12.  
  13. WITH d AS
  14. (
  15. SELECT *
  16. FROM sys.databases AS ds
  17. WHERE ds.name = 'tempdb'
  18. )
  19. SELECT TOP (@top) *
  20. FROM d;
  21.  
  22. END
  23.  
  24. IF @TopCount IS NULL OR @TopCount < 1
  25. SET @TopCount = POWER(2, 30) -- A big enough number (careful with overflows!)
  26.  
  27. WITH
  28. alias AS (...)
  29. SELECT TOP (@TopCount) * FROM alias;
  30.  
  31. WITH alias
  32. AS (...)
  33. SELECT TOP (CASE WHEN @TopCount>0 THEN @TopCount ELSE 2147483647 END) *
  34. FROM alias;
  35.  
  36. declare @TopCount int
  37.  
  38. SELECT
  39. @TopCount = CASE
  40. WHEN ISNULL(@TopCount,0) > 0
  41. THEN @TopCount
  42. ELSE count(1) END
  43. FROM alias
  44.  
  45. select top (@TopCount) *
  46. from alias
Add Comment
Please, Sign In to add comment