Create procedure [dbo].[sp_InsertUpdate]
(
@SetupDate datetime,
@ExamNumber varchar(20)
)
as
begin
insert into OnlineExamHdr
(
SetupDate,
ExamNumber
)
values
(
@SetupDate,
@ExamNumber
)
declare @ExamID int
declare @datept varchar(20)
declare @date varchar(20)
declare @num int
set @date = (select ExamSetupdate from Tbl1 where ExamID = @ExamID)
set @datept = convert(varchar(4), @date,100) + convert(varchar(4),year(@date))
set @num = (select top 1 substring(examnumber, 10, len(examnumber)) as num from Tbl1 where examnumber like @date+'%' order by Examid desc)
update Tbl1
set ExamNumber = (select(@datept + space(2) + convert(nvarchar(50), (right(100 + (@num + 1), 2)))))
where ExamID = @ExamID
end
Please anyone give the solution.