We help IT Professionals succeed at work.

sql server stored procedure parameter

pae2
pae2 asked
on
129 Views
Last Modified: 2017-04-04
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.

Author

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.
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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

Author

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

Author

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
I will try now.

Author

Commented:
EXEC [dbo].[GetInfo] '8939372'; works. Hardcoding works.
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions