sql procedure for select and insert at same time

Hassan Siddiqui

8y1.1kSQL

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.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Packiaraj Santhiyagu8y ago
Hi,

First set challean number column as identity.

Then use IDENT_CURRENT it will return last inserted number in the table and session.
Jignesh Kumar8y ago
Hi bikas, Is your question resolved ?
Kapil Singh Kumawat8y ago
You can use the Identity property or Sequence to enter the numbers in sequence
Jignesh Kumar8y ago
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
Cynthia Sathuragiri8y ago
1. Create PROCEDURE [dbo].[usp_ChallanNumberInsert]
2. AS
3. BEGIN TRY
4. SET NOCOUNT ON;
5. DECLARE @lastChallanNumber AS INT;
6. DECLARE @newChallanNumber AS INT;
7. --get the last entered challan number
8. SELECT @lastChallanNumber = Max([ChallanNumber]) FROM [dbo].[tblChallanNumber];
9. --add 1 to this
10. SET @newChallanNumber=@lastChallanNumber + 1;
11. --insert incremented challan number at the same table
12. INSERT INTO [dbo].[tblChallanNumber] (ChallanNumber) VALUES (@newChallanNumber);
13. --select the old challan number and the new challanNumber
14. SELECT @lastChallanNumber AS LastChallanNumber,@newChallanNumber AS NewChallanNumber
15. END TRY
16. BEGIN CATCH
17. DECLARE @ERRMSG AS VARCHAR (1000) = CAST (ERROR_NUMBER() AS VARCHAR (20)) + ': ' + ERROR_MESSAGE();
18. RAISERROR (@ERRMSG, 11, 1);
19. END CATCH
20. --If your table have the column ChallanNumber as Identity column then
21. -- use the below statements to get the last entered challan number
22. Create PROCEDURE [dbo].[usp_ChallanNumberInsert]
23. AS
24. BEGIN TRY
25. SET NOCOUNT ON;
26. DECLARE @lastChallanNumber AS INT;
27. DECLARE @newChallanNumber AS INT;
28. --get the last entered challan number
29. SELECT @lastChallanNumber = Max([ActionTypeID]) FROM [dbo].[tblChallanNumber];
30. INSERT INTO [dbo].[tblChallanNumber] ([OtherColumn])
31. VALUES ('Other Column values');
32. SELECT @newChallanNumber = SCOPE_IDENTITY();
33. --select the old challan number and the new challanNumber
34. SELECT @lastChallanNumber AS LastChallanNumber,@newChallanNumber AS NewChallanNumber
35. END TRY
36. BEGIN CATCH
37. DECLARE @ERRMSG AS VARCHAR (1000) = CAST (ERROR_NUMBER() AS VARCHAR (20)) + ': ' + ERROR_MESSAGE();
38. RAISERROR (@ERRMSG, 11, 1);
39. END CATCH

Bikash Sahu8y ago

Hi Hassan

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

Kapil Singh Kumawat8y ago

can you please post some details about your query with some example.

it will be easy to understand for us