Questions about variables in stored procedures

Question

I came across a stored procedure in my class and it has me puzzled. Under the alter procedure there are a bunch of variables that are neither declared or set. Are these not "variables" but parameters? I only know of parameters in the SSRS report aspect. I know what parameters do, they pass data, but I am not sure what these are meant for under the alter procedure. Do all of these variable/parameters have to be used? Are they optional?

Anyway, why are they there and how are they different from the declared variables?

GO
ALTER procedure [sys].[sp_addmergesubscription] (
    @publication                    sysname,                   
    @subscriber                     sysname = NULL,                
    @subscriber_db                  sysname = NULL,              
    @subscription_type              nvarchar(15) = 'push',            
    @subscriber_type                nvarchar(15) = 'local',            
    @subscription_priority          real        = NULL,                
    @sync_type                      nvarchar(15) = 'automatic',        
    @frequency_type                 int = NULL, -- defaults to 4
    @frequency_interval             int = NULL, -- defaults to 1
    @frequency_relative_interval    int = NULL, -- defaults to 1
    @frequency_recurrence_factor    int = NULL, -- defaults to 0
    @frequency_subday               int = NULL, -- defaults to 8
    @frequency_subday_interval      int = NULL, -- defaults to 1
    @active_start_time_of_day       int = NULL, -- defaults to 0
    @active_end_time_of_day         int = NULL, -- defaults to 235959
    @active_start_date              int = NULL, -- defaults to 0
    @active_end_date                int = NULL, -- defaults to 99991231
    @optional_command_line          nvarchar(4000) = NULL,
    @description                    nvarchar(255) = NULL,
    @enabled_for_syncmgr            nvarchar(5) = NULL, 
    -- Agent offload
    @offloadagent                   bit = 0,
    @offloadserver                  sysname = NULL,
    @use_interactive_resolver       nvarchar(5) = NULL,
    @merge_job_name                 sysname = NULL,
    @hostname                       sysname = NULL
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @retcode                int
    declare @subnickname            binary(6)
    declare @priority               real
    declare @subid                  uniqueidentifier
    declare @pubid                  uniqueidentifier    
    declare @subscriber_typeid      smallint
    declare @subscriber_srvid       int
    declare @merge_jobid            binary(16)            
    declare @subscription_type_id   int      
    declare @distproc               nvarchar(300)
    declare @command                nvarchar(255)
    declare @inactive               tinyint
    declare @subscriber_bit         smallint
    declare @global                 tinyint        
    declare @push                   tinyint        
    declare @sync_typeid            tinyint
    declare @nosync                 tinyint        
    declare @automatic              tinyint        
    declare @distributor            sysname
    declare @distribdb              sysname
    declare @publisher              sysname
    declare @publisher_db           sysname
    declare @found                  int
    declare @datasource_type        int
    DECLARE @platform_nt            binary
    declare @use_interactive_bit    bit
    declare @internal               sysname
    declare @REPOLEVersion_90           int
                ,@publishingservername sysname
    declare @compatlevel int

    /* make sure current database is enabled for merge replication */
    exec @retcode=sys.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
        return (1)

    /*
    ** Initializations.
    */
    set @datasource_type = 0    /* Default SQL Server */
    set @platform_nt = 0x1    
    SET @nosync                = 2          
    SET @automatic            = 1          
    set @inactive            = 0
    SET @subscriber_bit        = 4     
    set @global                = 1
    set @push                = 0
    set @pubid                = NULL           
    set @publisher            = publishingservername()
    set @publisher_db        = DB_NAME()
    select @found            = 1       /
            ,@publishingservername = publishingservername()
    set @REPOLEVersion_90    = 90

Answer 1

If you are asking about @publication, then it is a parameter of a stored procedure. This parameter doesn't have a default value, so it has to be specified when calling the procedure. Other parameters, like @subscriber have default values, so they are optional.

Stored procedures can have parameters.

A stored procedure communicates with the calling program through its parameters. When a program executes a stored procedure, it can pass values to the stored procedure through the parameters of the stored procedure. These values can be used as standard variables in the Transact-SQL programming language. The stored procedure can also return values to the calling program through OUTPUT parameters. A stored procedure can have as many as 2100 parameters, with each parameter having a name, data type, direction, and default value.

Some of them may have default values.

You can create a stored procedure with optional parameters by specifying a default value for optional parameters. When the stored procedure is executed, the default value is used if no other value has been specified.