sql server stored procedure parameter

Simply put, I am getting a single value from a temp table and assigning it to a variable.

I want to use that variable as a parameter within a call to another stored procedure. But it's not working. It is not syntax.

I get '0 rows affected'. The comments in my code list what works and what doesn't.

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

EXEC [master].[dbo].[GetInfo] @CID; -- THIS DOES NOT WORK!
-- EXEC [master].[dbo].[GetInfo] '4832712'; -- THIS WORKS FINE!

How can I get EXEC [master].[dbo].[GetInfo] @CID; to work?

Thanks!

pae2
pae2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
How can I get EXEC [master].[dbo].[GetInfo] @CID; to work?
this should working fine.

is GetInfo a stored procedure or function?

try to debug the GetInfo and make sure got relevant record(s) return there.
0
pae2Author Commented:
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.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
GetInfo works with a literal argument, just not a parameter
that shouldn't be the case...

can you share the codes of SP: GetInfo here?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

FREE DOWNLOAD
pae2Author Commented:
Yes I can. I will need a few minutes though.
0
pae2Author Commented:
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
0
pae2Author Commented:
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
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
so,... it seems nothing special in the SP: GetInfo but just to insert the relevant records into table: report.

and then you got a 0 rows affected message. hence, try to debug the select statement in your SP.

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)

Open in new window


try open a New Query in SSMS, and hard code the value of E.EMP_STATUS and @CID. See if you got anything returned there?
0
pae2Author Commented:
I will try now.
0
pae2Author Commented:
EXEC [dbo].[GetInfo] '8939372'; works. Hardcoding works.
0
pae2Author Commented:
The problem is indeed with the GetInfo sproc. The argument and filter both work. I should be able to resolve tomorrow with more extensive debugging. I will post tomorrow and assign the points to you Ryan Chong.

Thanks!

pae2
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.