Insert and then Update in a Single Stored Procedure

Question

question

Vanthiya Thevan asked Oct 18, '10

Insert and then Update in a Single Stored Procedure

Hello Sir, I am a Beginner. I have One problem. I want to insert and update records using single stored procedure. I have One table called Tbl1. It contains three columns such as ExamID, SetUpDate, ExamNumber. ExamID is an Identity Column. When I insert the SetUpDate, the ExamNumber will be automatically updated like Oct 2010 01(based on SetUpDate). In that Oct 2010 is the Month and Year of the SetUpDate and 01 is the automatically generated number. Likewise when i insert another record, the examnumber will be generated as Oct 2010 02 and so on.. Suppose if we select setupdate as Nov 2010. then the exam number will be updated as Nov 2010 01, 02 and so on… I have write one procedure but it is not working.


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.

sql-server-2005 sql

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-10-18T22:30:25Z

Cyborg answered Oct 18, '10

Vanthiya, This is my understanding, you have an ExamHeader table with column exam date and exam number, and ExamNumber column is just like a computed column based on the ExamSetupdate you need the ExamNumber column to be incremented, Eg: if the ExamSetupDate is 2010-10-22 (imagine its a fresh entry for that month and year) then you need ExamNumber to have 201010 01, and for 2010-10-25 ExamNumber should be 201010 02 and for 2010-11-05 ExamNumber should be 201011 01(image its again a fresh entry)




CREATE TABLE [dbo].[OnlineExamHdr]
    (
      [ExamID] [int] IDENTITY(1, 1)
                     NOT NULL ,
      [ExamSetupdate] [date] NULL ,
      [examnumber] [varchar](10) NULL
    )
ON  [PRIMARY]

GO

ALTER PROCEDURE [dbo].[sp_InsertUpdate] -- '2010-10-15'
    ( @SetupDate DATETIME )
AS 
BEGIN

    DECLARE @ExamNumber VARCHAR(10) ,
        @counter INT

    SET @ExamNumber = LEFT(CONVERT(VARCHAR, @Setupdate, 112), 6)
    SELECT  @counter = ISNULL(MAX(CONVERT(INT, RIGHT(ExamNumber,
                                                     LEN(ExamNumber) - 6))),
                              0) + 1
    FROM    OnlineExamHdr
    WHERE   LEFT(ExamNumber, 6) = LEFT(CONVERT(VARCHAR, @Setupdate, 112),
                                       6)



    SET @ExamNumber = @ExamNumber + ' ' + CONVERT(VARCHAR, @counter)

    INSERT  INTO OnlineExamHdr
            ( ExamSetupdate, ExamNumber )
    VALUES  ( @SetupDate, @ExamNumber )
                     

END


[sp_InsertUpdate] '2010-10-22'
GO
[sp_InsertUpdate] '2010-10-24'
GO
[sp_InsertUpdate] '2010-11-05'

SELECT * FROM OnlineExamHdr

-- Result set

ExamID	ExamSetupdate	examnumber
1	2010-10-22	201010 1
2	2010-10-24	201010 2
3	2010-11-05	201011 1

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ · Oct 19, 2010 at 01:19 AM

For bonus points and extra safety, wrap the functionality within the SP up in a transaction... ...and put a unique index constraint on the ExamNumber field.

2 ·

question