By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TYPE [dbo].[udt_RefNo] FROM [char](16) NOT NULL
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
))
INSERT dbo.table_name
(RefNo, S_Mode, ref_clnt)
VALUES
('1000', 'value', NULL);
1 rows affected
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')
| Ref |
|---|
| 1 |
| Ref |
|---|
| null |