Stored procedure hangs seemingly without explanation

I think that this is related to parameter sniffing and the need to parameterize your input params to local params within the SP. Adding with recompile causes the execution plan to be recreated and eliminates much of the benefits of having a SP. We were using With Recompile on many reports in an attempt to eliminate this hanging issue and it occassionally resulted in hanging SP's that may have been related to other locks and/or transactions accessing the same tables simultaneously. See this link for more details Parameter Sniffing (or Spoofing) in SQL Server and change your SP's to the following to fix this:

CREATE PROCEDURE [dbo].[SPNAME] @p1 int, @p2 int AS

DECLARE @localp1 int, @localp2 int

SET @localp1=@p1 SET @localp2=@p2

An answer of Brent Ozar might work, but it returns only active command text by default. For example, it returns WAITFOR DELAY '00:00:05' for query like:

CREATE PROCEDURE spGetChangeNotifications
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @actionType TINYINT;
    WHILE @actionType IS NULL
    BEGIN
        WAITFOR DELAY '00:00:05';
        SELECT TOP 1
            @actionType = [ActionType]
        FROM
            TableChangeNotifications;
    END;
    SELECT
        TOP 1000 [RecordID], [Component], [TableName], [ActionType], [Key1], [Key2], [Key3]
    FROM
        TableChangeNotifications;
END;

How it looks like:

Thus, check the parameter @get_outer_command as described here.

Also, try this one instead(slightly modified procedure from MS Docs):

DECLARE
    @sessions TABLE
(
    [SPID]        INT,
    STATUS        VARCHAR(MAX),
    [Login]       VARCHAR(MAX),
    [HostName]    VARCHAR(MAX),
    [BlkBy]       VARCHAR(MAX),
    [DBName]      VARCHAR(MAX),
    [Command]     VARCHAR(MAX),
    [CPUTime]     INT,
    [DiskIO]      INT,
    [LastBatch]   VARCHAR(MAX),
    [ProgramName] VARCHAR(MAX),
    [SPID_1]      INT,
    [REQUESTID]   INT
);

INSERT INTO @sessions
EXEC sp_who2;

SELECT
    [req].[session_id],
    [A].[Login] AS 'login',
    [A].[HostName] AS 'hostname',
    [req].[start_time],
    [cpu_time] AS 'cpu_time_ms',
    OBJECT_NAME([st].[objectid], [st].[dbid]) AS 'object_name',
    SUBSTRING(REPLACE(REPLACE(SUBSTRING([ST].text, ([req].[statement_start_offset] / 2) + 1, ((CASE [statement_end_offset]
                                                                                                   WHEN -1
                                                                                                       THEN DATALENGTH([ST].text)
                                                                                                       ELSE [req].[statement_end_offset]
                                                                                               END - [req].[statement_start_offset]) / 2) + 1), CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS [statement_text],
    [ST].text AS 'full_query_text'
FROM
    sys.dm_exec_requests AS req
        CROSS APPLY
    sys.dm_exec_sql_text(req.sql_handle) AS ST
        LEFT JOIN @sessions AS A
            ON A.SPID = req.session_id
ORDER BY
    [cpu_time] DESC;

How it looks like:

Of course, it's possible to modify code from Brent Ozar answer so it would select a full query text, too, though. Nearly same technique is chosen there(link of code of 18.07.2020 so might change after time):

I had the same problem today and I don't know what causes it but I found a solution. I took the input parameter and saved it into a new parameter, i.e.

declare @parameter2 as x = @parameter

Then i changed the references to the parameter in the queries from @parameter to @parameter2.