sql server stored procedure parameter

GetInfo is a stored procedure. GetInfo works with a literal argument, just not a parameter. The output of GetInfo goes to a table and it works with a literal, every time.

Yes I can. I will need a few minutes though.

You're right. I wrote a small replacement sproc that did work. I will post the other code though that still has the problem. But see below. All of this works:

SELECT CID='4832712'
INTO #ChangeTracking

SELECT CID AS COLUMNVIATMPTABLE
FROM #ChangeTracking;

DECLARE @CID VARCHAR(10);
SET @CID = (SELECT TOP 1 CID FROM #ChangeTracking)
-- SELECT @CID            -- THIS WORKS FINE!

EXEC [master].[dbo].[GetInfo] @CID; -- THIS DOES WORK!

The small SPROC replacement that I created is this:

CREATE PROCEDURE [dbo].[GetInfo] @CID VARCHAR(10)
AS
SELECT @CID AS COLUMNINPROC

The following is the problem code, but I had to update it by removing most of the irrelevant code. The updated code will show you the essence of the code. Don't worry about the joins or anything in SELECT. I think my problem might be with the sproc structure with regard to the parameter or perhaps because the parameter is in the filter. Please LMK what ya think!

CREATE PROCEDURE [dbo].[GetInfo] @CID VARCHAR(10)
AS

BEGIN

INSERT INTO [master].[dbo].[report]

SELECT DISTINCT
            E.USERNAME AS accountName,
            P.FIRST_NAME AS givenName,
             P.LAST_NAME AS sn,
             E.EMP_TITLE AS title,

PS.ADDR_MODIFIER_LINE      E

FROM [db].[dbo].[PERSON] P
WHERE E.EMP_STATUS = 'XX'
OR P.ID IN (SELECT @CID)

END

I will try now.

EXEC [dbo].[GetInfo] '8939372'; works. Hardcoding works.