Convert this sql sript into stored procedure

Question

I have some bunch of scripts (sql commands) here that I would like to use as stored procedure .. Anybody can help ..

The reason I want to convert this script is for easy use on my Web Application query.

  /* 1. Select all the duplicates */
  SELECT
        [PTAID]
      ,[RequestID]
      ,[RequestDate]
      ,[ProvName]
      ,[Amount]
      ,[INorOUT]
      ,[Supplier]
      ,[Customer]
      ,[Program]
      ,[IssueDesc]
      ,[Chargeable]
      , COUNT(*) as [Count]
 FROM [PFTracking].[dbo].[TempTable]
GROUP BY
   [PTAID],[RequestID]
      ,[RequestDate]
      ,[ProvName]
      ,[Amount]
      ,[INorOUT]
      ,[Supplier]
      ,[Customer]
      ,[Program]
      ,[IssueDesc]
      ,[Chargeable]
HAVING 
    COUNT(*) > 1

    --2. Add Temp Column

alter table [PFTracking].[dbo].[TempTable] 
   add sno int identity(1,1) 

   -- 3. Remove duplicates 

   delete from [PFTracking].[dbo].[TempTable]
    where sno in(select sno 
    from (
    select *,RANK()
     OVER ( PARTITION BY [PTAID],[RequestID]
      ,[RequestDate]
      ,[ProvName]
      ,[Amount]
      ,[INorOUT]
      ,[Supplier]
      ,[Customer]
      ,[Program]
      ,[IssueDesc]
      ,[Chargeable] ORDER BY sno DESC )rank
     From [PFTracking].[dbo].[TempTable])T 
     where rank>1 )
     alter table [PFTracking].[dbo].[TempTable] drop  column sno

And can please anybody help me how to call this stored procedure on ASP.NET Web App ?

Additional :

    if not exists(select * from sys.servers where name=N'CNCTC-WEB01')
begin

exec sp_addlinkedserver @server='CNCTC-WEB01'

exec sp_addlinkedsrvlogin 'CNCTC-WEB01','false',null,'svc_Phils','Apple@6'

end

INSERT INTO [PFTracking].[dbo].[TempTable]
 SELECT 
    c.[pf_id]
  ,a.[RequestDate]
  ,c.[pf_carrierUsed]
  ,b.[PiecePrice] * b.[PartQuantity] as [Amount]
  ,c.[pf_type]
  ,c.[pf_resSupplier]
  ,c.[pf_resCustomer]
  ,c.[pf_trailerNum]
  ,b.[PartDesc]
  ,c.[pf_chargeBack]
  ,c.[pf_chargetoPlant]
 FROM [CNCTC-WEB01].[NOP_PR].[dbo].[Requests] a
   JOIN [CNCTC-WEB01].[NOP_PR].[dbo].[Parts] b on a.[RequestID] = b.[RequestID]
   JOIN [PHRIZ-WEBAPP01].[PFTracking].[dbo].[Tbl_PFExcel] c on  b.[PartNumber] like '%'+c.pf_id+'%'
 where a.[EntityName] like '%PTA' 
AND a.[RequestDate] between '2015-04-20 00:00:00.000' AND GETDATE()

Answer 1

You don't need to create a temporary table and add a temporary column to delete the duplicates. A single query will do. Here is how to do it using CTE:

CREATE PROCEDURE RemoveDuplicatesTempTable
AS
BEGIN

SET NOCOUNT ON;

WITH Cte AS(
    SELECT *,
        RN = RANK() OVER (
                PARTITION BY 
                    [PTAID]
                    ,[RequestID]
                    ,[RequestDate]
                    ,[ProvName]
                    ,[Amount]
                    ,[INorOUT]
                    ,[Supplier]
                    ,[Customer]
                    ,[Program]
                    ,[IssueDesc]
                    ,[Chargeable] 
                ORDER BY sno DESC) 
     From [PFTracking].[dbo].[TempTable]
)
DELETE FROM Cte WHERE RN > 1

END

You can read more on stored procedure here.

To call the stored procedure from you ASP.Net application:

using (SqlConnection con = new SqlConnection(_connectionString))
{
    using (SqlCommand cmd = new SqlCommand("RemoveDuplicatesTempTable", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        con.Open();
        cmd.ExecuteNonQuery();
    }
}