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