Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.
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?
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
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?
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
Thanks!
pae2
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
is GetInfo a stored procedure or function?
try to debug the GetInfo and make sure got relevant record(s) return there.