sql procedure for select and insert at same time

Question

Hi dear reader thanks for devoting your precious time. Here my question is i have a table tblChallanNumber i want to write a store procedure such that 1st i select the last entered challan number and then add 1 to this and then insert incremented challan number at the same table. and finally i want to select the new challanNumber to print . and importantly i want to do the above all work in a singe store procedure. thanks.

Kapil Singh Kumawat · Answer

You can use the Identity property or Sequence to enter the numbers in sequence

Jignesh Kumar · Answer

Hi, Please use this for you increamental coulumn and just set Identity(1,1) Create table ChallanDetail ( ChallanId int identity(1,1) not null col1 varchar (50), col2 varchar (50) ) identity value automatically increament by 1 . You no need to pass your challanId in insert procedure, it will automatically handle by Idenetity(1,1) CREATE procedure Usp_InsertChallanDetails As begin insert into Challandetails (col1,col2) values ('col1value', 'col2Value') End Hope it will help

Bikash Sahu · Answer

Hi Hassan If your table already have identity property then ,use SCOPE_IDENTITY() to get last entered challan number.

Kapil Singh Kumawat · Answer

can you please post some details about your query with some example. it will be easy to understand for us

Packiaraj Santhiyagu · Answer

Hi, First set challean number column as identity. Then use IDENT_CURRENT it will return last inserted number in the table and session.

Jignesh Kumar · Answer

Hi bikas, Is your question resolved ?

Cynthia Sathuragiri · Answer

Create PROCEDURE [dbo].[usp_ChallanNumberInsert] AS BEGIN TRY SET NOCOUNT ON ; DECLARE @lastChallanNumber AS INT ; DECLARE @newChallanNumber AS INT ; --get the last entered challan number SELECT @lastChallanNumber = Max ([ChallanNumber]) FROM [dbo].[tblChallanNumber]; --add 1 to this SET @newChallanNumber=@lastChallanNumber + 1; --insert incremented challan number at the same table INSERT INTO [dbo].[tblChallanNumber] (ChallanNumber) VALUES (@newChallanNumber); --select the old challan number and the new challanNumber SELECT @lastChallanNumber AS LastChallanNumber,@newChallanNumber AS NewChallanNumber END TRY BEGIN CATCH DECLARE @ERRMSG AS VARCHAR (1000) = CAST (ERROR_NUMBER() AS VARCHAR (20)) + ': ' + ERROR_MESSAGE(); RAISERROR (@ERRMSG, 11, 1); END CATCH --If your table have the column ChallanNumber as Identity column then -- use the below statements to get the last entered challan number Create PROCEDURE [dbo].[usp_ChallanNumberInsert] AS BEGIN TRY SET NOCOUNT ON ; DECLARE @lastChallanNumber AS INT ; DECLARE @newChallanNumber AS INT ; --get the last entered challan number SELECT @lastChallanNumber = Max ([ActionTypeID]) FROM [dbo].[tblChallanNumber]; INSERT INTO [dbo].[tblChallanNumber] ([OtherColumn]) VALUES ( 'Other Column values' ); SELECT @newChallanNumber = SCOPE_IDENTITY(); --select the old challan number and the new challanNumber SELECT @lastChallanNumber AS LastChallanNumber,@newChallanNumber AS NewChallanNumber END TRY BEGIN CATCH DECLARE @ERRMSG AS VARCHAR (1000) = CAST (ERROR_NUMBER() AS VARCHAR (20)) + ': ' + ERROR_MESSAGE(); RAISERROR (@ERRMSG, 11, 1); END CATCH