I'm trying to determine the next customer code using a 3 digit customer code. If you pass in "ABC", and there are already 3 rows for
customer ABC, then you should get bacl 'ABC-004'. Here's what I have:

CREATE PROCEDURE ap_GetNextProjectNoForClient
	@CustomerCode	VARCHAR(03),
	@NewCustCode	VARCHAR(07) OUTPUT
	
AS
BEGIN
	
	DECLARE @Count INT

	SELECT @Count = COUNT(CustomerCode)
		FROM COSProjects
		WHERE CustomerCode =  @CustomerCode

	SET @NewCustCode = @CustomerCode + '-' + dbo.PADL(@Count, 3, '0')

I'm calling it like this:

DECLARE @Code VARCHAR(3)
EXEC ap_GetNextProjectNoForClient 'MCD', @Code OUTPUT
SELECT @Code

All I'm getting back is the @CustomerCode value.

If I put this at the end, it works:

SELECT @CustomerCode + '-' + dbo.PADL(@Count, 3, '0')