sql server service broker queue pass parameter to stored procedure

Stored Procedure

Create PROCEDURE [dbo].[InitServiceLog] 
(
@name varchar(max)=null
)
AS
BEGIN   
    SET NOCOUNT ON; 
    insert into [AdventureWorks].[dbo].[ServiceBroker_Log](Service) values(@name)
END

Service Broker Queue calling Stored Procedure Name "InitServiceLog"

CREATE QUEUE dbo.SSBSLearningInitiatorQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION
(
STATUS = ON ,
PROCEDURE_NAME =[dbo].[InitServiceLog], 

///// is there any way to pass Stored                                        
///// Procedures Parameters from here
///// Like this "PROCEDURE_NAME =[dbo].[InitServiceLog]('log')

MAX_QUEUE_READERS = 10,
EXECUTE AS OWNER
)
ON [PRIMARY]
GO

In above query Queue how to call stored procedure

No, activation stored procedures are called "in a vacuum" so they don't allow passing parameters (there's "nothing" calling it) nor is there even a user context in which to execute, which is why the EXECUTE AS syntax exists and is required for activation.

If you need to abstract the logic, it can be separated into two procedures. The activation procedure is parameterless, but it can call another with parameters. Something like this:

CREATE PROCEDURE [dbo].[CentralizedLogic]
(
    @p1 varchar(MAX) = NULL
)
AS
BEGIN
    ...
END

CREATE PROCEDURE [dbo].[QueueActivator]
AS
BEGIN
    EXEC [dbo].[CentralizedLogic] @p1 = 'blah';
END

CREATE QUEUE [dbo].[MyQueue]
    WITH
        ...
        ACTIVATION
        (
            ...
            PROCEDURE_NAME = [dbo].[QueueActivator]
            ...
        );

StackExchange Database Administrators Q#58354, answer score: 4