-- =============================================
-- Author:		Jas Rademeyer
-- Create date: 10-02-2017
-- Description:	Get next surface item code available in range 
-- Search based on user ids
-- =============================================
CREATE PROC sp_GetNextSurfaceItemCodeByUserId
( @userId INT )
AS

SELECT	CASE WHEN C.lastUsed = 0
			THEN C.Code + CAST(C.rangeStart AS VARCHAR)
			ELSE CASE WHEN (C.lastUsed + 1) > C.rangeEnd
					THEN '-1'
					ELSE C.Code + CAST(C.lastUsed + 1 AS VARCHAR)
				END 
		END
FROM	pal_SurfaceItemCode C
WHERE	C.userId = @userId
		AND C.[status] = 'Active'
GO

