Monday, July 10, 2006

What I do for a living...(example one)

Sometimes I write stuff like this...

-- Retrieve the EwaResponse_DMTrigger(s) that currently exist for P&G and store it in a temp table for later comparison --

CREATE TABLE #ExistingEwaResponse_DMTriggers (
EwaResponseID INT,
DMTriggerID INT,
UserID INT
)

INSERT INTO #ExistingEwaResponse_DMTriggers

SELECT
EwaResponse_DMTrigger.EwaResponseID,
EwaResponse_DMTrigger.DMTriggerID,
EwaResponse.UserID
FROM
EwaResponse_DMTrigger WITH (NOLOCK)
INNER JOIN
EwaResponse WITH (NOLOCK)
ON EwaResponse.EwaResponseID = EwaResponse_DMTrigger.EwaResponseID
WHERE
EwaResponse_DMTrigger.EwaResponseID IN (

SELECT
EwaResponse.EwaResponseID
FROM
EwaResponse WITH (NOLOCK)
INNER JOIN
Users WITH (NOLOCK)
ON Users.UserID = EwaResponse.UserID
INNER JOIN
Site WITH (NOLOCK)
ON Site.SiteID = Users.SiteID
WHERE
Site.CompanyID = 474)

ORDER BY
EwaResponse_DMTrigger.EwaResponseID DESC
---------------------------------

-- Execute spGetDMForm for all P&G EwaResponse(s) --
DECLARE
@EwaResponseID INT,
@UserID INT

DECLARE
LOOPYcursor CURSOR FOR

SELECT
EwaResponse.EwaResponseID,
EwaResponse.UserID
FROM
EwaResponse WITH (NOLOCK)
INNER JOIN
Users WITH (NOLOCK)
ON Users.UserID = EwaResponse.UserID
INNER JOIN
Site WITH (NOLOCK)
ON Site.SiteID = Users.SiteID
WHERE
Site.CompanyID = 474
ORDER BY
EwaResponse.EwaResponseID DESC

OPEN LOOPYcursor

FETCH NEXT FROM LOOPYcursor INTO
@EwaResponseID,
@UserID

WHILE @@Fetch_Status = 0
BEGIN

EXECUTE [dbo].[spGetDMForm] @EwaResponseID, @UserID, null

FETCH NEXT FROM LOOPYcursor INTO
@EwaResponseID,
@UserID

END

CLOSE LOOPYcursor

DEALLOCATE LOOPYcursor
---------------------------------

-- Retrieve the EwaResponse_DMTrigger(s) that are new for P&G --
SELECT
CurrentEwaResponse_DMTrigger.EwaResponseID,
CurrentEwaResponse_DMTrigger.DMTriggerID,
CurrentEwaResponse_DMTrigger.UserID
FROM
(
SELECT
EwaResponse_DMTrigger.EwaResponseID,
EwaResponse_DMTrigger.DMTriggerID,
EwaResponse.UserID
FROM
EwaResponse_DMTrigger WITH (NOLOCK)
INNER JOIN
EwaResponse WITH (NOLOCK)
ON EwaResponse.EwaResponseID = EwaResponse_DMTrigger.EwaResponseID
) AS CurrentEwaResponse_DMTrigger
WHERE
CurrentEwaResponse_DMTrigger.EwaResponseID IN
(
SELECT
EwaResponse.EwaResponseID
FROM
EwaResponse WITH (NOLOCK)
INNER JOIN
Users WITH (NOLOCK)
ON Users.UserID = EwaResponse.UserID
INNER JOIN
Site WITH (NOLOCK)
ON Site.SiteID = Users.SiteID
WHERE
Site.CompanyID = 474
)
AND NOT EXISTS
(
SELECT
EwaResponseID,
DMTriggerID,
UserID
FROM
#ExistingEwaResponse_DMTriggers
WHERE
#ExistingEwaResponse_DMTriggers.EwaResponseID =
CurrentEwaResponse_DMTrigger.EwaResponseID

AND #ExistingEwaResponse_DMTriggers.DMTriggerID =
CurrentEwaResponse_DMTrigger.DMTriggerID

AND #ExistingEwaResponse_DMTriggers.UserID =
CurrentEwaResponse_DMTrigger.UserID

)
ORDER BY
CurrentEwaResponse_DMTrigger.EwaResponseID DESC
---------------------------------

-- Drop the temp table created --
DROP TABLE #ExistingEwaResponse_DMTriggers
---------------------------------

No comments: