October 23, 2015 at 10:28 am
I have the following information in a Control Table that sets the parameters for a n archive stored procedure as well an the batch size and the Active Indicator which is designed to stop the Stored Procedure.
I have not touched the code.
I'm having issues with the code that OUPUT To section of the code.
It does not delete any data because the variable @Call_Date is bad logic and does not meet the criteria for a Delete.
This is the Stored Procedure;
CREATE PROCEDURE [dbo].[usp_ArchivetblCall]
AS
DECLARE @TableName VARCHAR (50)
DECLARE @BatchSize int
DECLARE @ArchiveYear CHAR(4)
DECLARE @RowCount int
SET @TableName = 'tblCall'
SET @BatchSize = (SELECT BatchSize FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = @TableName)
SET @ArchiveYear = (SELECT ArchiveYear FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = @TableName)
DECLARE @Call_Date Date
SET @Call_Date = (SELECT TOP 1 Call_date
FROM PrismData.[dbo].[tblCall] tc)
----------------------------------------------------------------------
-- ??
--SET @Active_Indicator =
--COALESCE((SELECT 1 FROM PrismDataArchive.dbo.ArchiveDriver
-- WHERE Active_Indicator = 1 AND TableName = @TableName),0)
DECLARE @Active_Indicator bit
SET @Active_Indicator = (SELECT Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = @TableName)
WHILE @BatchSize > 0 AND @TableName = 'tblCall' AND @Active_Indicator = 1
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
IF @Call_Date IS NULL
BEGIN
DELETE TOP (@BatchSize) FROM tc
OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive
FROM PrismData.[dbo].[tblCall] tc
WHERE tc.Call_Date IS NULL
END
ELSE
BEGIN
DELETE TOP (@BatchSize) FROM tc
OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive
FROM PrismData.[dbo].[tblCall] tc
WHERE tc.Call_Date >= DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0) --jan 1 of the year of the date param passed.
AND tc.Call_Date < DATEADD(yy,1,DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0)) --add one year:
END
SET @BatchSize = @@ROWCOUNT; -- capture number of rows affected
IF @BatchSize > 0
BEGIN
INSERT INTO ArchiveLog (TableName,ArchiveYear, BatchSize)
VALUES (@TableName,@ArchiveYear,@BatchSize)
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH
END
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 23, 2015 at 10:43 am
It does not delete any data because the variable @Call_Date is bad logic and does not meet the criteria for a Delete.
Since the deletes are based upon this variable, and the logic is bad, if this logic was fixed would that make the procedure run?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 23, 2015 at 12:22 pm
Michael L John (10/23/2015)
It does not delete any data because the variable @Call_Date is bad logic and does not meet the criteria for a Delete.
Since the deletes are based upon this variable, and the logic is bad, if this logic was fixed would that make the procedure run?
Someone added it.
I worked month ago when I created it but it has been changed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 23, 2015 at 2:41 pm
So I guess the tblCall table has only one row? Otherwise @Call_Date could be set to any row value from that table. That would make it almost impossible to debug. Perhaps it should be like this?:
SET @Call_Date = (SELECT TOP 1 Call_date
FROM PrismData.[dbo].[tblCall] tc ORDER BY Call_date /*DESC*/)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply