Sproc using cursor

Transact-SQL
,
1

Hello,

I have the following table and Stored Procedure:

   CREATE TABLE [dbo].[myChecks](
    	[CheckID] [int] IDENTITY(1,1) NOT NULL,
    	[CustomerID] [int] NULL,
    	[Category] int NULL,
    	[CheckDescr] [nvarchar](50) NULL,
    	[CheckResult] [bit] NULL
    )
    GO
    INSERT INTO [dbo].[myChecks] (CustomerID,Category,CheckDescr,CheckResult)
    VALUES (1,1,'Check1',1)
    INSERT INTO [dbo].[myChecks] (CustomerID,Category,CheckDescr,CheckResult)
    VALUES (1,1,'Check2',1)
    INSERT INTO [dbo].[myChecks] (CustomerID,Category,CheckDescr,CheckResult)
    VALUES (4,1,'Check3',1)
     INSERT INTO [dbo].[myChecks] (CustomerID,Category,CheckDescr,CheckResult)
    VALUES (4,1,'Check1',NULL)
    INSERT INTO [dbo].[myChecks] (CustomerID,Category,CheckDescr,CheckResult)
    VALUES (4,1,'Check2',1)
    INSERT INTO [dbo].[myChecks] (CustomerID,Category,CheckDescr,CheckResult)
    VALUES (1,1,'Check3',1)
    INSERT INTO [dbo].[myChecks] (CustomerID,Category,CheckDescr,CheckResult)
    VALUES (2,6,'Check1',1)
    INSERT INTO [dbo].[myChecks] (CustomerID,Category,CheckDescr,CheckResult)
    VALUES (2,6,'Check4',0)
    INSERT INTO [dbo].[myChecks] (CustomerID,Category,CheckDescr,CheckResult)
    VALUES (2,6,'Check5',1)
    INSERT INTO [dbo].[myChecks] (CustomerID,Category,CheckDescr,CheckResult)
    VALUES (2,6,'Check6',0)

CREATE PROCEDURE dbo.TestCursor
(@CustomerID int)
AS
BEGIN

DECLARE @CheckDescr as nvarchar(50);
DECLARE @CheckResult as bit;
DECLARE @Status int;
 
DECLARE @CheckCursor as CURSOR;
 
SET @CheckCursor = CURSOR FOR
SELECT CheckDescr,CheckResult
FROM dbo.myChecks
WHERE CustomerID=@CustomerID
 
OPEN @CheckCursor;
FETCH NEXT FROM @CheckCursor INTO @CheckDescr, @CheckResult;
 
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @Status= CASE @CheckResult
    WHEN 1 THEN 1
	WHEN 0 THEN 0
	ELSE -1
  END
  IF @Status=1
    FETCH NEXT FROM @CheckCursor INTO @CheckDescr, @CheckResult;
  ELSE 
   BREAK;
 
END
 
CLOSE @CheckCursor;
DEALLOCATE @CheckCursor;

END
GO

With the above code I manage to get @Status=1 if all records have CheckResult=1, otherwise I get 0 when one of the records is 0 or -1 if one of the records is null.
The problem is that I want to check the records based on Category. If for example the Category=1 then I must have 3 rows with values (Check1, Check2, Check3) on CheckDescr and if that is true then to go and check the value of CheckResult.

Any kind of help would be appreciated.
Thank you in advance.

2

Forget the cursor, that will just slow the processing, and it's not needed for what you're doing, at least as I understand it.

You should instead be able to use MAX() and HAVING to do whatever comparisons you need, but you haven't fully explained what you're trying to do.

SELECT 
    /* ???? */
    CASE WHEN 
        MAX(CASE WHEN Category=1 AND CheckDescr = 'Check1' THEN 1 ELSE 0 END) +
        MAX(CASE WHEN Category=1 AND CheckDescr = 'Check2' THEN 1 ELSE 0 END) +
        MAX(CASE WHEN Category=1 AND CheckDescr = 'Check3' THEN 1 ELSE 0 END) = 3
        AND MAX(CheckResult) = 1 
        THEN 1
        /*WHEN ...*/
        ELSE 0
        END AS Status
FROM dbo.myChecks
WHERE CustomerID=@CustomerID
1 Like
3

Thank you for your answer! How do I navigate through records with your solution?

I have more than one records for the same CustomerID, so I need to check that I have all Check1,Check2,Check3 in rows (not in the same row) and all Checks have CheckResult as 1. For example:

If category=1 then I must have the following 3 rows in order to get Status=1 ({1,1,'Check1',1},{1,1,'Check2',1},{1,1,'Check3',1})
If category==2 tthen Status=1 would be if rows are: ({5,2,'Check4',1},{5,2,'Check5',1},{5,2,'Check6',1},{5,2,'Check7',1})

4

You shouldn't have to navigate thru all the rows yourself. The code above can check all the rows as a group for each CustomerID and set the status accordingly. Looping is not done in relational-based code unless it's absolutely unavoidable, since it's vastly slower than using set-based logic within the db engine. T-SQL should not be written like C# or Java.

But you haven't explained the logic for the comparisons, so I can't help you any further.

5

I thought I did, i'll try to be more accurate :slight_smile:

I have a table with records after different checks. The result of the check (CheckResult) is set to 1 if the check is valid and 0 if not.
The checks are not the same for all categories. Each category has different amount of checks.

What I have to do is:
A) based on category i must have records for all the check type (checkdescr) one record for every check - be certain than i'm not missing records
B) if all records are valid then return 1 else 0.