So I was looking at the code for the proc created by Ola's script and noticed a number of variable declarations happening early on (like at line 10) but these variable declarations do not have the DECLARE keyword prefacing the variable identifier.

Then, a couple lines lower, a new batch of variables are declared, but this time with the DECLARE keyword.

I was under the impression that you always needed the DECLARE. Is it a scope thing? Like related to the fact the first set of variables are before the stored proc's begin statement?

Is this undocumented shorthand or can anyone point to documentation?

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[DatabaseBackup]    Script Date: 7/20/2015 2:23:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DatabaseBackup]

@Databases nvarchar(max),
@Directory nvarchar(max) = NULL,
@BackupType nvarchar(max),
@Verify nvarchar(max) = 'N',
@CleanupTime int = NULL,
@CleanupMode nvarchar(max) = 'AFTER_BACKUP',
@Compress nvarchar(max) = NULL,
@CopyOnly nvarchar(max) = 'N',
@ChangeBackupType nvarchar(max) = 'N',
@BackupSoftware nvarchar(max) = NULL,
@CheckSum nvarchar(max) = 'N',
@BlockSize int = NULL,
@BufferCount int = NULL,
@MaxTransferSize int = NULL,
@NumberOfFiles int = NULL,
@CompressionLevel int = NULL,
@Description nvarchar(max) = NULL,
@Threads int = NULL,
@Throttle int = NULL,
@Encrypt nvarchar(max) = 'N',
@EncryptionAlgorithm nvarchar(max) = NULL,
@ServerCertificate nvarchar(max) = NULL,
@ServerAsymmetricKey nvarchar(max) = NULL,
@EncryptionKey nvarchar(max) = NULL,
@ReadWriteFileGroups nvarchar(max) = 'N',
@OverrideBackupPreference nvarchar(max) = 'N',
@NoRecovery nvarchar(max) = 'N',
@URL nvarchar(max) = NULL,
@Credential nvarchar(max) = NULL,
@MirrorDirectory nvarchar(max) = NULL,
@MirrorCleanupTime int = NULL,
@MirrorCleanupMode nvarchar(max) = 'AFTER_BACKUP',
@LogToTable nvarchar(max) = 'N',
@Execute nvarchar(max) = 'Y'

AS

BEGIN

  ----------------------------------------------------------------------------------------------------
  --// Source: https://ola.hallengren.com                                                          //--
  ----------------------------------------------------------------------------------------------------

  SET NOCOUNT ON

  DECLARE @StartMessage nvarchar(max)
  DECLARE @EndMessage nvarchar(max)
  DECLARE @DatabaseMessage nvarchar(max)
  DECLARE @ErrorMessage nvarchar(max)

These first ones are parameters to the stored procedure, not variable declarations. Parameters don't need DECLARE.

Carl

• Proposed as answer by Naomi NModerator 10 hours 29 minutes ago
• Marked as answer by zentechinc 10 hours 16 minutes ago

Oh heavens,

I normally lay out the proc like: db.schema.storedProc Param2, Param2, etc.

Seeing the Parameters placed below threw me off.

Embarrassed and sorry for wasting your time on a curiosity and brain fart.

Oh heavens,

I normally lay out the proc like: db.schema.storedProc Param2, Param2, etc.

Seeing the Parameters placed below threw me off.

Embarrassed and sorry for wasting your time on a curiosity and brain fart.

Do not feel bad. The T-SQL dialect is based on a simple one-pass compiler from the old Sybase days. The @ prefix tells the compiler that this is local data and it does not care about how it is used, parameter or variable.

In the SQL/PSM standards, you prefix a parameter with IN, OUT and IN OUT to show how the parameter  data is passed. This is why I usually put "@in_<parameter name>" in my T-SQL headers. I strip them off with a simple text edit when i port code or need know if it is a local variable or parameter.

This same one-pass design is why we still have @, @@, # and ## prefixes.