title: SQL Stored Procedure Get Distinct and Update
body: <p>First let me start by saying I can do the below in .NET but I want it in the right place and putting less stress on the server. So I might be leanring towards .NET in the way I have tried to build it! </p>
  
  <p>Query/Aim: </p>
  
  <p>To show how many times a user (by EmployeeID) is in the TblTableList and then update the TblTableStatusCount</p>
  
  <p>For now I am assuming the EmployeeID Exists. I have looked at varies example on stackoverflow, including cursors but to be honest I am a little confused and would be delighted with any guidance. </p>
  
  <p>Also need to understand how to pass the value to @Status0Count etc.. </p>
  
  <p>Using: Microsoft SQL Server 2008 (.NET2 in this case but usually 3.5 or 4) </p>
  
  <p>Code:</p>
  
  <pre><code> USE [Database]
      GO
      /****** Object:  StoredProcedure [dbo].[spUpdatePassword]    Script Date: 07/16/2014 10:15:58 ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      ALTER PROCEDURE [dbo].[spActionsDashboardSitesSummary]
          -- Add the parameters for the stored procedure here
  
      @EmployeeID NvarChar(10), --These may change to limited size i.e. 10
      @Status0 Int,
      @Status1 Int,
      @Status2 Int,
      @Status3 Int
  
      AS
      BEGIN
  
      SELECT * FROM [TblTableMain] 
  
        BEGIN
          SELECT COUNT(DISTINCT(ID)) FROM TblTableList WHERE EmployeeID = @EmployeeID  
          SELECT COUNT(DISTINCT(ID)) FROM TblTableList WHERE EmployeeID = @EmployeeID AND CurrentAlertStatus = '0'
          SELECT COUNT(DISTINCT(ID)) FROM TblTableList WHERE EmployeeID = @EmployeeID AND CurrentAlertStatus = '1'
          SELECT COUNT(DISTINCT(ID)) FROM TblTableList WHERE EmployeeID = @EmployeeID AND CurrentAlertStatus = '2'
  
          -- Check if ID Exists       
  
          IF EXISTS (SELECT @EmployeeID FROM TblTableStatusCount WHERE EmployeeID = @EmployeeID)
  
              UPDATE TblTableStatusCount 
              SET StatusTotalCount = @StatusTotal, Status0Count = @Status0, Status1Count = @Status1, Status2Count = @Status2
              WHERE EmployeeID = @EmployeeID
            Else
              INSERT INTO TblTableStatusCount 
              EmployeeID , StatusTotalCount, Status0Count, Status1Count , Status2Count) VALUES (@EmployeeID , @StatusTotal,@Status0, @Status1, @Status2 )
              WHERE EmployeeID = @EmployeeID
  
          END
  
      END
  </code></pre>
  
  <p>I am more than happy to clarify anything</p>
  
  <p><strong>EDIT - UPDATED CODE WITH SELECT and DECLARE</strong></p>
  
  <pre><code>  USE [database] 
  GO
  /****** Object:  StoredProcedure [dbo].[spUpdatePassword]    Script Date: 07/16/2014 10:15:58 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  Alter PROCEDURE [dbo].[spActionsDashboardSitesSummary]
      -- Add the parameters for the stored procedure here
  
  AS
  
  BEGIN
  
  DECLARE @StatusTotal AS Int
  DECLARE @Status0 AS Int
  DECLARE @Status1 AS Int
  DECLARE @Status2 AS Int
  DECLARE @Status3 AS Int
  
  --Cursor to loop through
  DECLARE @PropertyID  AS NvarChar(10);
  DECLARE propCurs CURSOR FOR
  SELECT  PropertyID FROM [tbPropertyDetails]
  
  --Open Loop Code
  OPEN propCurs;
  FETCH NEXT FROM propCurs INTO @PropertyID
  WHILE @@FETCH_STATUS = 0  
  --Now do repeated code
  
    BEGIN
  
   --   SELECT @StatusTotal = COUNT(ID) FROM TblActions WHERE PropertyID = @PropertyID  
      --SELECT @Status0 =  COUNT(DISTINCT(ID)) FROM TblActions WHERE PropertyID = @PropertyID AND CurrentAlertStatus = '0'
      --SELECT @Status1 =  COUNT(DISTINCT(ID)) FROM TblActions WHERE PropertyID = @PropertyID AND CurrentAlertStatus = '1'
      --SELECT @Status2 =  COUNT(DISTINCT(ID)) FROM TblActions WHERE PropertyID = @PropertyID AND CurrentAlertStatus = '2'
      --SELECT @Status3 =  COUNT(DISTINCT(ID)) FROM TblActions WHERE PropertyID = @PropertyID AND CurrentAlertStatus = '3'
  
  with cnt as ( 
     SELECT 
       1 as cntAll 
       , case WHEN CurrentAlertStatus = '0' then 1 else 0  end as cnt1
       , case WHEN CurrentAlertStatus = '1' then 1 else 0  end as cnt2
       , case WHEN CurrentAlertStatus = '2' then 1 else 0  end as cnt3
        , case WHEN CurrentAlertStatus = '3' then 1 else 0  end as cnt4
     from TblActions where PropertyID = @PropertyID
  )
  SELECT @StatusTotal = sum(cntAll),  
    @Status0  = sum(cnt1),
    @Status1  = sum(cnt2),
    @Status2  = sum(cnt3),
    @Status3  = sum(cnt4)
    FROM cnt;
      -- Check if ID Exists
  
      --HERE NEED TO CHECK IF EXISTS OR SHOULD I WIPE THE TABLE AT THE START? 
      IF EXISTS (SELECT PropertyID FROM [TblActionsCount] WHERE @PropertyID = PropertyID)
  
          UPDATE TblActionsCount 
          SET StatusTotalCount = @StatusTotal, Status0 = @Status0, Status1 = @Status1, Status2 = @Status2, Status3 = @Status3
          WHERE PropertyID = @PropertyID
        Else
          INSERT INTO [TblActionsCount] 
          (PropertyID, StatusTotalCount, Status0, Status1 , Status2, Status3) VALUES (@PropertyID, @StatusTotal,@Status0, @Status1, @Status2,@Status3 )
  
      END
  
      -- Now we bookend the loop code
  
      FETCH NEXT FROM propCurs INTO @PropertyID
  
  END
  
  --Tidy up and close loop code
  CLOSE propCurs;
  DEALLOCATE propCurs;
  </code></pre>