October 1, 2014 at 8:04 am
Dear All,
I really need your help on this, i am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18
2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I hope my description was clear and looking forward for your help please.
I have attached the scripts also, the line i described in step1 is
Best Regards
Nader
October 1, 2014 at 8:22 am
Most people won't download RAR files. Please post the code using IFCode tags [ code="sql"][/code] or at least text files. We would also need DDL and sample data. Please, read the article linked in my signature to know what we need.
October 1, 2014 at 12:12 pm
Luis Cazares (10/1/2014)
Most people won't download RAR files. Please post the code using IFCode tags [ code="sql"][/code] or at least text files. We would also need DDL and sample data. Please, read the article linked in my signature to know what we need.
+1.
I wouldn't download a RAR file that gets posted by someone I don't know personally and without your code there isn't any way to see what your issue is. My best guess is that you are trying something like this:
EXEC @cnt = GetCommonItemCount
But what that returns is the return value of the stored procedure which is 0 when executed without errors and not set directly, not any data from it. You either want something like this:
Declare @countTable Table(Column list)
Insert into @countTable (Column list matching the order in the sp)
Exec GetCommonItemCount
Or you want @cnt to be an output parameter in the stored procedure and call it like this:
/* I'm assuming you already have declared @cnt */
Exec GetCommonItemCount @cnt = @cnt Output
Select @cnt /* or use @cnt how you need to */
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 1, 2014 at 1:45 pm
Dear All,
Thanks you very much for your replies.
I have reattached the files and attached script to fill data as well.
I will try what you have mentioned and let you know results.
Here is how i call the sp
exec CheckBoxAvailability 119,59,0,1
Best Regards
Nader
October 1, 2014 at 1:51 pm
Hi Jack,
Thanks for your reply, your guess is right.
Please tell me how to use the example you wrote
Exec GetCommonItemCount @cnt = @cnt Output
Do you mean i should define an additional parameter to GetCommonItemCount and make the sp return the result in that parameter to the original SP?
Jack Corbett (10/1/2014)
Luis Cazares (10/1/2014)
Most people won't download RAR files. Please post the code using IFCode tags [ code="sql"][/code] or at least text files. We would also need DDL and sample data. Please, read the article linked in my signature to know what we need.+1.
I wouldn't download a RAR file that gets posted by someone I don't know personally and without your code there isn't any way to see what your issue is. My best guess is that you are trying something like this:
EXEC @cnt = GetCommonItemCountBut what that returns is the return value of the stored procedure which is 0 when executed without errors and not set directly, not any data from it. You either want something like this:
Declare @countTable Table(Column list)
Insert into @countTable (Column list matching the order in the sp)
Exec GetCommonItemCount
Or you want @cnt to be an output parameter in the stored procedure and call it like this:
/* I'm assuming you already have declared @cnt */
Exec GetCommonItemCount @cnt = @cnt Output
Select @cnt /* or use @cnt how you need to */
October 1, 2014 at 2:23 pm
Here's a link that explains Output Parameters, which you should read.
So here's how I'd modify the procedure to return @cnt as an output parameter. I'd probably make some other changes as well, but for now I'll just do the one.
In the SP Declaration do this:
ALTER PROC [dbo].[GetCommonItemCount]
@RequestID INT,
@BoxGroup INT,
@Dosage_form_code INT,
@license_type_code INT,
@cnt INT = NULL OUTPUT
AS
Then wherever you are returning a result set, assuming you aren't using this procedure anywhere else expecting a result set back. Do this:
IF NOT EXISTS ( SELECT
*
FROM
BoxRequestDet
WHERE
ParentKey = @RequestID )
BEGIN
SET @cnt = 0;
END;
...
IF EXISTS ( SELECT
*
FROM
WHERE
status = 'Same' )
BEGIN
SET @cnt = (
SELECT
COUNT(*)
FROM
WHERE
status = 'Same'
);
END;
ELSE
BEGIN
SET @cnt = 0;
END;
Then your call is going to look like:
exec GetCommonItemCount @RequestID =@RequestNo , @BoxGroup = @BoxGroup , @Dosage_form_Code = @DosageForm , @license_type_code = @CompType, @cnt = @cnt Output;
Then your outer procedure stays the same from there.
Edit: Added link.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 2, 2014 at 12:57 am
Dear Jack,
Thank you very much , i appreciate your help, it worked finally.
I will continue testing the data and let you know result.
Best Regards
Nader
October 2, 2014 at 2:27 am
Hi Jack,
I have changed the stored procedure(GetCommonItemCount) that i call from within the main stored procedure(CheckBoxAvailability).
Now when i call CheckBoxAvailability as follows
exec CheckBoxAvailability 119,59,0,1
sometimes i get result as one record set and sometimes as two, i guess first one is the execution status as you mentioned before, i only need one record set which is the result of my stored procedure, i tried addding set nocount on but still the same.
Appreciate your feedback
Thanks
Nader
Jack Corbett (10/1/2014)
Here's a link that explains Output Parameters, which you should read.So here's how I'd modify the procedure to return @cnt as an output parameter. I'd probably make some other changes as well, but for now I'll just do the one.
In the SP Declaration do this:
ALTER PROC [dbo].[GetCommonItemCount]
@RequestID INT,
@BoxGroup INT,
@Dosage_form_code INT,
@license_type_code INT,
@cnt INT = NULL OUTPUT
AS
Then wherever you are returning a result set, assuming you aren't using this procedure anywhere else expecting a result set back. Do this:
IF NOT EXISTS ( SELECT
*
FROM
BoxRequestDet
WHERE
ParentKey = @RequestID )
BEGIN
SET @cnt = 0;
END;
...
IF EXISTS ( SELECT
*
FROM
WHERE
status = 'Same' )
BEGIN
SET @cnt = (
SELECT
COUNT(*)
FROM
WHERE
status = 'Same'
);
END;
ELSE
BEGIN
SET @cnt = 0;
END;
Then your call is going to look like:
exec GetCommonItemCount @RequestID =@RequestNo , @BoxGroup = @BoxGroup , @Dosage_form_Code = @DosageForm , @license_type_code = @CompType, @cnt = @cnt Output;Then your outer procedure stays the same from there.
Edit: Added link.
October 2, 2014 at 7:45 am
So I tried setting up a test environment using the files you attached, but there is a reference to BoxDosageMapping and V in CheckBoxAvailability and your test setup doesn't include that data. And then GetCommonItemCount references TradeDrug which isn't in your test data setup either.
Based on looking at the stored procedure though you have later calls to GetCommonItemCount, and did you set them up the same way as the first call?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 3, 2014 at 2:41 am
Dear Jack,
While testing with different values , i notice the number of record sets coming is different so after debugging i found out that in come cases the result is outputted as old way not like new output parameter way.
After changing them it's perfect now.
I would like to thank you again for your excellent support.
Regards
Nade
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply