SELECT @Ref= CAST(MAX(ISNULL(CAST(ref_clnt AS INT),0))+1 AS VARCHAR(10))
FROM table_name WITH(NOLOCK)
WHERE s_mode='value'

SELECT @Ref = ref_clnt+1 FROM table_name WITH(NOLOCK)
WHERE RefNo = (SELECT MAX(RefNo) FROM table_name WHERE s_mode = 'value')

CREATE TABLE [dbo].[table_name](
    [RefNo] [dbo].[udt_RefNo] NOT NULL,
    [S_Mode] [varchar](10) NOT NULL,
    [ref_clnt] [varchar](50) NULL)
CONSTRAINT [PK_table_name] PRIMARY KEY CLUSTERED
(
    [RefNo] ASC
)

CREATE TYPE [dbo].[udt_RefNo] FROM [char](16) NOT NULL
GO