CODEWITHSUNDEEP
sqlsql-serversql-server-2008
indofraiser
indofraiser

Reputation: 1024

SQL Stored Procedure Get Distinct and Update

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!

Query/Aim:

To show how many times a user (by EmployeeID) is in the TblTableList and then update the TblTableStatusCount

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.

Also need to understand how to pass the value to @Status0Count etc..

Using: Microsoft SQL Server 2008 (.NET2 in this case but usually 3.5 or 4)

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

I am more than happy to clarify anything

EDIT - UPDATED CODE WITH SELECT and DECLARE

  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;

Upvotes: 1

Views: 1594

Answers (2)

xacinay
xacinay

Reputation: 909

You need to assgin @Status0,... appropriate value. Try to do it that way:

SELECT @Status0 = COUNT(DISTINCT(ID)) FROM TblTableList 
  WHERE EmployeeID = @EmployeeID;
SELECT @Status1 = COUNT( ...

Also, count query can be simplified with CTE (Common Table Expression):

; 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
   from TblTableList where EmployeeID = @EmployeeID
)
select @StatusTotal = sum(cntAll),  
  @Status0  = sum(cnt1),
  @Status1  = sum(cnt2),
  @Status2  = sum(cnt3),
  from cnt;

I supposed, your ID is a unique Primary Key and therefore no DISTINCTfiltering necessary

EDIT:

Cursor around the code above:

DECLARE @PropertyID int;
DECLARE propCurs CURSOR FOR  
SELECT PropertyID
FROM tbPropertyDetails;

OPEN propCurs
FETCH NEXT FROM propCurs INTO @PropertyID
WHILE @@FETCH_STATUS = 0   
BEGIN 
  ...--code here

  FETCH NEXT FROM propCurs INTO @PropertyID;  
END   
CLOSE propCurs;
DEALLOCATE propCurs;

Upvotes: 1

Recursive
Recursive

Reputation: 952

You can use the same query as @StatusTotal but without distinct.That would fetch the number of times the user in the table.distinct always gives only '1' since we have id in where clause.

Upvotes: 0

Related Questions