try this example...already i used this same requirement;
ALTER PROCEDURE [dbo].[SP_AutoNumber]
@strGUID uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
if exists(select * from TABLENAME where GUID = @strGUID)
begin
SELECT RIGHT(REPLICATE('0', 4) + CAST(CAST(COALESCE(MAX(columnname), 0) AS INT) AS VARCHAR), 4) dcno
FROM
(
SELECT TOP 1 columnname[generatecolumn]
FROM Tablename
ORDER BY sno DESC
) q;
end
else
begin
SELECT RIGHT(REPLICATE('0', 4) + CAST(CAST(COALESCE(MAX(columnname), 0) AS INT) + 1 AS VARCHAR), 4) dcno
FROM
(
SELECT TOP 1 columnname[generatecolumn]
FROM tablename
ORDER BY sno DESC
) q;
end
END