Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
GetInfo works with a literal argument, just not a parameterthat shouldn't be the case...
can you share the codes of SP: GetInfo here?
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
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
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
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.
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?
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)
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?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial