Avatar of pae2
pae2
Flag for United States of America asked on

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
Microsoft SQL Server

Avatar of undefined
Last Comment
pae2

8/22/2022 - Mon
Ryan Chong

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.
pae2

ASKER
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.
ASKER CERTIFIED SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
pae2

ASKER
Yes I can. I will need a few minutes though.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
pae2

ASKER
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
pae2

ASKER
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
SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
pae2

ASKER
I will try now.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
pae2

ASKER
EXEC [dbo].[GetInfo] '8939372'; works. Hardcoding works.
SOLUTION
pae2

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.