Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Please don't cross-post questions - this was also posted on Stack Overflow -stackoverflow.com/questions/21601982/…. If you feel that this is a better fit for DBA.SE, then flag your question for the moderators to move it or delete it from Stack Overflow before posting it on another site. –  bluefeet Feb 6 at 12:07
    
@vivek Please delete your question here. –  Aaron Bertrand Feb 7 at 4:30
    
deleted from here stackoverflow.com/questions/21601982 –  vivek Feb 7 at 4:37

1 Answer 1

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]
            ...
        );
share|improve this answer
    
Thanks Jon.Thanks a lot. –  vivek Feb 6 at 15:05
    
@vivek: You're welcome. –  Jon Seigel Feb 6 at 16:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.