-1

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()
Anaiah
  • 633
  • 7
  • 20
  • 2
    Step #1 read up SPROCs - when/where they are useful, and how to create them, etc. You will want to remove the DDL (alter table) if possible. In this case it would probably be best to eliminate the temp table (or "column") entirely .. but that has nothing to do with 'convert to a stored procedure'. – user2864740 Jun 17 '15 at 00:45
  • Do you want to delete the duplicate? – Felix Pamittan Jun 17 '15 at 00:45
  • @wewesthemenace .. Yes .. I want to remove the duplicate .. I mean that is the idea of the script I wanted to do .. – Anaiah Jun 17 '15 at 00:47

1 Answers1

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();
    }
}
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67