How to get the record with the same values except primary key

Suppose the records in database are as below:

Id         FirstName       LastName        OrderPrice
1          Tom             Hanks           123
2          Tom             Hanks           173
3          Tom             Hanks           123
4          Tom             Bob             123
5          Robert          Hanks           123

Given Input : Id = 1
Expected OUtput : Id = 3

I know that I can write some thing like below

Declare @Id as INT = 1
Declare @FirstName as varchar(100)
Declare @LastName as varchar(100)
Declare @OrderPrice as INT

Select @FirstName = FirstName, @LastName  = LastName , @OrderPrice = OrderPrice  From Customers Where Id= @ID

Select ID From Customers Where FirstName = @FirstName and LastName = @L开发者_运维知识库astName and OrderPrice = @OrderPrice

But, I do not want to write some thing like where I should mention all the columns names in where condition. Because actually the record I was working on contains almost 100 columns in it. So, I was asked not to use a query some thing like this.

Can any one help me how to do this?

You can use dynamic SQL to create a query to search for duplicate records. Basically, this query builds a string like a.col1 = b.col1 and a.col2 = b.col2 and ... and uses that as the on clause in a self-join:

declare @columns varchar(max)

select  @columns = 
            case when @columns is null then 'a.' else @columns + ' and a.' end +
            quotename(name) + ' = b.' + quotename(name)
from    sys.columns
where   object_id = object_id('YourTable')
        and name <> 'ID'

declare @query varchar(max)
set @query = 'select b.ID from YourTable a ' +
    'join YourTable b on a.ID <> b.ID and ' + @columns + ' where a.ID = 1'
exec (@query)

This searches for rows that duplicate ID = 1, except for the ID.

Declare @Id as INT = 1

WITH
    dupes AS
    (
        SELECT
C1.Id,
            C1.FirstName,
            C1.LastName,
            C1.OrderPrice
        FROM
            Customers C1
        LEFT OUTER JOIN Customers C2
        ON
            C1.FirstName = C2.FirstName
        AND C1.LastName = C2.LastName
        AND C1.OrderPrice = C2.OrderPrice
        WHERE
            C1.Id <> C2.Id
    )
SELECT
    dupes.Id
FROM
    Customer
INNER JOIN dupes
ON
    Customer.FirstName = dupes.FirstName
AND Customer.LastName = dupes.LastName
AND Customer.OrderPrice = dupes.OrderPrice
WHERE
   Customer.Id = @Id
AND dupes.Id <> @Id