How to Create Stored Procedure search to last id

Abdalla Elawad

10y1.2kVisual Basic .NET

Hi Guys

I have a table contains to three field any one is PK

(TeamNo.)

(BankName)

(City)

(Id) is Identity

i want to create stored procedures in order to serach by three fields are above and at the same time i select to last record into insert a table according to three fielad(

TeamNo.)

(BankName)

(City) for last id inserted for any teams in a certain Bank and City .Please see table below :

Thanks

13 Replies

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

Suthahar Jegatheesan9y ago
HI,
Use
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
get last value from store procedure .
Thanks
Suthahar.J
Bikesh Srivastava9y ago
I think nitin is correct...
Rajan Singh9y ago
hello Abdalla Elawad,
your requirement is not clear but you want to get id when you insert data in table.

CREATE PROCEDURE [dbo].[sp_Charity_Search]
@Id int
,@Name nvarchar(max)
,@age nvarchar(max)
AS
BEGIN
DECLARE @IDS INT
INSERT INTO [dbo].[tbl_Blogs]
(Name
,age)
VALUES
(@Name,
@age
)
SET @IDS=@@IDENTITY
END
SELECT @IDS
Pradeep Sahoo10y ago
Your requirements are unclear . Plz post with detailed information.
Nitin Sontakke10y ago
I don't have performance statistics at hand, but I believe that the Max(Id) would be quicker than select top(1) and order by.

For example,

select max(id)
from OperationPlan
where TeamNo=@TeamNo AND City=@City AND BankName=@BankName
Abdalla Elawad10y ago
USE [Cash_Plan]
GO
/****** Object: StoredProcedure [dbo].[R2] Script Date: 10/31/2016 10:01:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[R2]
(@TeamNo NVARCHAR(50),
@BankName NVARCHAR(50) ,
@City NVARCHAR(50))

AS
BEGIN
select top (1) * from OperationPlan
--order by ID DESC
WHERE
TeamNo=TeamNo AND City=@City AND BankName=@BankName
ORDER BY ID DESC END

______________________________________________________________
THIS IS SOLUTION (THANK YOU FOR ALL
Abdalla Elawad10y ago
by More explain i upload the table and screen interface i need
save and search code by (last id ,TeamNo,BankName,City)
Figure Below :
Abdalla Elawad10y ago
Full Details for my problem: I have a table Contains from Three fields Are Primary Key (TeamNo.(Pk), City (Pk),BankName (Pk) Also table Contain to Id as identity. I want to create search Command By Three Primaray Key mentined above at any month Same TeamNo. and city and BankName is Repeated according to Id I need for this in Rotation Team within month contiously . What is solution ? Thank you .
Manas Mohapatra10y ago
Please describe more to understand the requirement. If possible please provide an example..
Amit Gupta10y ago
Select * from tablename where teamno = 'search' and city = 'search' and bankname = 'search'
//replace tablename with your tablename
// replace search with your searchText
Abdalla Elawad10y ago
i need to search by (teamno , City, bankname)
please help me
Abdalla Elawad10y ago
hi guys
Amit Gupta10y ago
Use max (id)+1 and go for the creation of store procedure.

Link fo creating store procedure https://msdn.microsoft.com/en-us/library/ms345415.aspx