How to execute a stored procedure with another stored procedure?

Question

I have session lock procedure which do the action of when one procedure is running in one session if the same procedure is running another session it will not execute until the first session completes

EXEC dbo.[system-LockProcedure]'dbo.usp_Test1'

It is working fine and I have no issues but when I want to execute procedure with input parameters, I get an error:

DECLARE @threadid INT = 0;

EXEC [util].[system-LockProcedure] N'[dbo].[usp_Test1] @threadid=@threadid', 
                       N'@threadid INT', 
                         @threadid=@threadid

Error:

[dbo].[usp_Test1] expects parameter @threadid, which is not supplied.

When I'm executing a procedure with no input parameters it is working fine and when I want execute a procedure with input parameters, it throws an error.

Please suggest how to fix this.

System Lock Procedure :

CREATE Procedure [dbo].[system-LockProcedure] @procname varchar(200)

AS 

--BEGIN TRAN
BEGIN

DECLARE @lockreturn int,
        @lockresource varchar(200)               --sysname


SELECT @lockresource =    @procname    



              -- The below line will try to acquire an exclusive lock on the PROC for the session, If the Proc is already in execution the @lockreturn value will be > 0

                 EXEC @lockreturn = sp_getapplock @lockresource, @LockMode = 'Exclusive', @LockOwner = 'Session'  , @LockTimeout = 100
                                                                Print @lockreturn 

                 IF @lockreturn <> 0
                 BEGIN

                        RAISERROR ('Another instance of the procedure is already running', 16, 1)
                                                                                                RETURN
                 END

                -- The Code to be executed goes here. All the core logic of the proc goes here..

              Print 'Procedure Execution Started for user: ' +  cast (CURRENT_USER as nvarchar(20))+ ' for session: ' + cast (@@SPID as nvarchar(10))

                -- This is just to make the system wait for 30 seconds and make sure if there is any concurrent execution triggered will fail

                                                                exec @lockresource

              Print 'Procedure Execution Ended for user: ' +  cast (CURRENT_USER as nvarchar(20))+ ' for session: ' + cast (@@SPID as nvarchar(10))

                                                  Print @lockreturn

                -- This is to release the lock once the SP code is ran completely

                                                EXEC sp_releaseapplock @lockresource , 'Session'

                                                 -- END TRY


    END

what is `system-LockProcedure` ? – Squirrel Jun 07 '19 at 04:49 — Squirrel, Jun 07 '19 at 04:49

score 2 · Answer 1 · answered Jun 07 '19 at 04:49

2

Try this:

DECLARE @threadid INT = 0;

DECLARE @ExecuteString NVARCHAR(MAX) = N'[dbo].[usp_Test1] @threadid= ' + CAST(@threadid AS VARCHAR(4)) + ';'

EXEC [util].[system-LockProcedure] @ExecuteString;

answered Jun 07 '19 at 04:49

gotqn

42,737
46
157
243

why you end up with ';'? – A Farmanbar Jun 07 '19 at 04:54
1

@Mr.AF It is a good practice all statements to end with the statement terminator `;`. Not putting it is a deprecated behavior - https://stackoverflow.com/a/26356175/1080354 – gotqn Jun 07 '19 at 04:55
@mohan111 xm.. strange - so you have change the value of the input parameter to be nvarchar, right? – gotqn Jun 07 '19 at 11:38
yes @gotqn I have just tried that way but it is not working – mohan111 Jun 07 '19 at 12:54

score 0 · Answer 2 · edited Jun 07 '19 at 05:09

0

Before the end of the stored procedure you use procedure name who wants to execute and its corresponding parameter like :

Alter procedure Procedure1
As
begin
  select * from TableName
  Exec Procedure2 
end

edited Jun 07 '19 at 05:09

Arulkumar

12,966
14
47
68

answered Jun 07 '19 at 04:47

Ravi Sharma

362
1
5

It is the locking session procedure I need to call procedure from outside only – mohan111 Jun 07 '19 at 04:50

How to execute a stored procedure with another stored procedure?

2 Answers2

Linked