sqlserver.sqlserver 1.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172
  1. -- =============================================
  2. -- Author: Morgan Yarbrough
  3. -- Create date: 4/27/2015
  4. -- Description: Test procedure that shows off language features.
  5. -- Includes non-standard folding with region comments using either
  6. -- line comments or block comments (both are demonstrated below).
  7. -- This mode imitates SSMS and it designed to be used with SQL Server theme.
  8. -- =============================================
  9. CREATE PROCEDURE dbo.TestProcedure
  10. --#region parameters
  11. @vint INT = 1
  12. ,@vdate DATE = NULL
  13. ,@vdatetime DATETIME = DATEADD(dd, 1, GETDATE())
  14. ,@vvarchar VARCHAR(MAX) = ''
  15. --#endregion
  16. AS
  17. BEGIN
  18. /*#region set statements */
  19. SET NOCOUNT ON;
  20. SET XACT_ABORT ON;
  21. SET QUOTED_IDENTIFIER ON;
  22. /*#endregion*/
  23. /**
  24. * These comments will produce a fold widget
  25. */
  26. -- folding demonstration
  27. SET @vint = CASE
  28. WHEN @vdate IS NULL
  29. THEN 1
  30. ELSE 2
  31. END
  32. -- another folding demonstration
  33. IF @vint = 1
  34. BEGIN
  35. SET @vvarchar = 'one'
  36. SET @vint = DATEDIFF(dd, @vdate, @vdatetime)
  37. END
  38. -- this mode handles strings properly
  39. DECLARE @sql NVARCHAR(4000) = N'SELECT TOP(1) OrderID
  40. FROM Orders
  41. WHERE @OrderDate > GETDATE()'
  42. -- this mode is aware of built in stored procedures
  43. EXECUTE sp_executesql @sql
  44. -- demonstrating some syntax highlighting
  45. SELECT Orders.OrderID
  46. ,Customers.CompanyName
  47. ,DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AS FirstDayOfYear
  48. FROM Orders
  49. INNER JOIN Customers
  50. ON Orders.CustomerID = Customers.CustomerID
  51. WHERE CompanyName NOT LIKE '%something'
  52. OR CompanyName IS NULL
  53. OR CompanyName IN ('bla', 'nothing')
  54. -- this mode includes snippets
  55. -- place your cusor at the end of the line below and trigger auto complete (Ctrl+Space)
  56. createpr
  57. -- SQL Server allows using keywords as object names (not recommended) as long as they are wrapped in brackets
  58. DATABASE -- keyword
  59. [DATABASE] -- not a keyword
  60. END