y********o
发帖数: 61 | 1 请问这个procedure干什么用的？sql 蝌蚪，读不懂啊～～求高人指点
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 | m******u
发帖数: 12400 | 2 stored procedure. it is a kind of object of database. | r**********d
发帖数: 510 | 3 you arent clear about the syntax or logic.
if former, better to start with something simple.
if latter, ask co worker.
my understanding is that
it DQ_VALIDATE TABLE and fields of the table when table id is passed in,
or all the tables returned from the cursor.
then
it updates start end time to track the DQ process in DQ_VAL(id, VAL_
START_DT, VAL_END_DT )
@fldID is not used.
read DQ_VALIDATE_TABLE may help you understand it better | x****e
发帖数: 1773 | 4 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) -- This proc takes @tablID and @fldID arguments.
-- No idea what this proc does without knowing about the procs it calls, and
all the tables that are involved in the whole process.
AS
BEGIN
SET NOCOUNT ON
declare @VAL_ID bigint; -- Declare an @VAL_ID as bigint
--
INSERT INTO DQ_VAL(VAL_START_DT) values(GETDATE()); -- This table should
have an IDENTITY column.
-- This statement inserts a row with the VAL_START_DT value as the
current timestamp.
SELECT @VAL_ID = @@IDENTITY; -- Get the value for @VAL_ID as the @@
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 a cursor for the TAB_ID values from the DQ_TAB
table.
-- You could run this query and find out what values are to
be included. Can improve the performance by modifying the ISNULL.
DECLARE @id BIGINT; -- Declare an @id for looping over the cursor
OPEN c;
FETCH NEXT FROM c INTO @id;
WHILE @@FETCH_STATUS = 0 -- While loop.
BEGIN
EXEC DQ_VALIDATE_TABLE @VAL_ID, @ID; -- Calls two procedures, need to
check what these two would do. Presumably these two procs may also do sth
against the DQ_CAL table.
EXEC DQ_VALIDATE_FIELD @VAL_ID, @ID
FETCH NEXT FROM c INTO @id; -- Repeat with the next @id from the
cursor.
END
-- Loop ends.
CLOSE c;
DEALLOCATE c;
UPDATE DQ_VAL SET VAL_END_DT = GETDATE() WHERE VAL_ID = @VAL_ID; --
Updates the VAL_END_DT value with the current timestamp for the previously
inserted row.
END
GO
=
【在 y********o 的大作中提到】
: 请问这个procedure干什么用的？sql 蝌蚪，读不懂啊～～求高人指点
: 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 =
|
|
