Create Function For Generate Code of Database
ALTER FUNCTION [dbo].[GetMemberRegNo] ( @AccountID INT ) RETURNS VARCHAR(20) AS BEGIN DECLARE @CodeLengthInTable AS INT DECLARE @LastCodeInTable AS VARCHAR(20) DECLARE @LastCodeValue AS INT DECLARE @LatestCode AS VARCHAR(20) DECLARE @VARCODE AS VARCHAR(2) SET @VARCODE='DS' DECLARE @TOTALPatient AS BIGINT SELECT @TOTALPatient=COUNT(PatientID) FROM dbo.Patient WITH(NOLOCK) WHERE Patient_AccountID=@AccountID BEGIN SET @LastCodeValue= (ISNULL(@TOTALPatient,0) +1 ) IF LEN(@LastCodeValue)=1 SET @LatestCode=@VARCODE + '0000' + CONVERT(VARCHAR,@LastCodeValue) ELSE IF LEN(@LastCodeValue)=2 ...