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
@tt
WHERE
status = 'Same' )
BEGIN
SET @cnt = (
SELECT
COUNT(*)
FROM
@tt
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.