Who can tell me what this procedure wants to do?

Question

Who can tell me what this procedure wants to do?

kkbear

SSC-Addicted

Points: 457
More actions
July 13, 2015 at 2:31 pm

#318976

My boss sent me a task as below. Anyone can help me understand the procedure script first, and then tell me what I should do in this task? Thank you.
----------------------------------------------------------------------------------------------------------------------------------------
"The process to start with is the DQ_VALIDATE procedure in the CFH_ODS database. The tables it uses are the following:
DQ_TAB: Table Mapping
DQ_FLD: Field Mapping
DQ_VAL: Parent record for each validation attempt
DQ_VAL_TAB: Results for each table validation
DQ_VAL_FLD: Results for each field validation"
---------DQ_VALIDATE procedure Script--------------------------------------------------------------------------------------------
USE [CFH_ODS]
GO
/****** Object: StoredProcedure [dbo].[DQ_VALIDATE] Script Date: 07/13/2015 15:08:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DQ_VALIDATE](@tableID bigint = null, @fldID bigint = null)
AS
BEGIN
SET NOCOUNT ON
declare @VAL_ID bigint;
--
INSERT INTO DQ_VAL(VAL_START_DT) values(GETDATE());
SELECT @VAL_ID = @@IDENTITY;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT TAB_ID FROM DQ_TAB WHERE ISNULL(@tableID, TAB_ID) = TAB_ID and VAL_FL = 1;
DECLARE @id BIGINT;
OPEN c;
FETCH NEXT FROM c INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC DQ_VALIDATE_TABLE @VAL_ID, @ID;
EXEC DQ_VALIDATE_FIELD @VAL_ID, @ID
FETCH NEXT FROM c INTO @id;
END
CLOSE c;
DEALLOCATE c;
UPDATE DQ_VAL SET VAL_END_DT = GETDATE() WHERE VAL_ID = @VAL_ID;
END
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

It looks like it uses one optional parameter and then calls two other stored procedures for every record returned by the cursor.

SELECT TAB_ID FROM DQ_TAB WHERE ISNULL(@tableID, TAB_ID) = TAB_ID and VAL_FL = 1;

EXEC DQ_VALIDATE_TABLE @VAL_ID, @ID;

EXEC DQ_VALIDATE_FIELD @VAL_ID, @ID;

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Maybe go ask your boss for clarification? You're not really giving us enough information on the task to help.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass