SQL Server Performance Forum – Threads Archive

storedprocedure creation

Hi
I have created the stored procedure like this but i need the records generated by the following query should not be displayed and to be deleted anyone plz help me SELECT X.*
FROM MyTable X
INNER JOIN
(SELECT lastname, firstname
FROM MyTable
GROUP BY lastname, firstname
HAVING COUNT(*) > 1) Y
ON X.lastname = Y.lastname and X.firstname = Y.firstname
ORDER BY X.lastname, X.firstname USE testdb;
GO
IF OBJECT_ID ( ‘usp_getallrec’, ‘P’ ) IS NOT NULL
DROP PROCEDURE usp_getallrec;
GO
CREATE PROCEDURE usp_getallrec
AS
SELECT lastname, firstname, salution, MAX(address)
FROM MyTable
GROUP BY lastname, firstname, salution
GO
SELECT X.*
FROM MyTable X
INNER JOIN
(SELECT lastname, firstname
FROM MyTable
GROUP BY lastname, firstname
HAVING COUNT(*) > 1) Y
ON X.lastname = Y.lastname and X.firstname = Y.firstname
ORDER BY X.lastname, X.firstname
sql server

---

delete d
from myTable d inner join
(
SELECT lastname, firstname, salution, MAX(address) as max_address
FROM MyTable
GROUP BY lastname, firstname, salution
) a
on d.lastname = a.lastname
and d.firstname = a.firstname
and d.salutaion = a.salution
adn d.address = a.max_address KH

---

hi khtan
this query you sent me is working fine. sql server

---

]]>