Syntax error

Question

Post reply

Syntax error

MrXspektra

Old Hand

Points: 358
More actions
April 13, 2018 at 3:39 am

#344376

CREATE PROCEDURE [dbo].[usp_test]
AS
SET NOCOUNT ON
BEGIN
BEGIN TRY
  DECLARE @First_Partition_No INT = 1;
  DECLARE @Last_Partition_No INT = 31;
  DECLARE @Date INT = DATEPART(day, GETDATE());
  DECLARE @Counter INT = 0;
  DECLARE @BackupDays INT = 7; -- Number of days to keep the backup
  WHILE @First_Partition_No <= @Last_Partition_No
  BEGIN
   IF @First_Partition_No <= (@Date - @BackupDays) OR @First_Partition_No > @Date
   BEGIN
    SET @Counter = @Counter + 1;
    TRUNCATE TABLE dbo.StatementData WITH (PARTITIONS(@First_Partition_No))
    TRUNCATE TABLE dbo.ProofOfAccountData WITH (PARTITIONS(@First_Partition_No))
    TRUNCATE TABLE dbo.PaidupLetterData WITH (PARTITIONS(@First_Partition_No))

   END
   SET @First_Partition_No = @First_Partition_No + 1;
   IF @Counter = (@Last_Partition_No - @BackupDays)
   BEGIN
    BREAK
   END
  END
END TRY
BEGIN CATCH -- Error Message for try catch
   DECLARE @Err_Msg nvarchar(MAX) = null
   SET @Err_Msg = 'SQL ERROR:' + ERROR_MESSAGE()
   RAISERROR (@Err_Msg, 16, 1);
END CATCH
END
What am i doing wrong?
Msg 319, Level 15, State 1, Procedure usp_test, Line 20
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Procedure usp_test, Line 21
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Procedure usp_test, Line 22
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Viewing 6 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You appear to be using SQL Server 2012. The WITH PARTITIONS syntax is only available in SQL Server 2016 and higher.

John

MrXspektra Old Hand Points: 358 More actions · Answer 2

MrXspektra

Old Hand

Points: 358

April 13, 2018 at 3:56 am

#1986719

Do you perhaps have a link confirming this?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

John Mitchell-245523

SSC Guru

Points: 148809

April 13, 2018 at 4:01 am

#1986723

Just search the documentation for TRUNCATE.

John

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

The documentation is pretty clear on this. https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sergiy SSC Guru Points: 110209 More actions · Answer 5

I'd suggest to drop the partitioning and all the mess associated with it.
Create clustered index started with [BackupDate] on each of the tables.
When retrieving data for a specific backup use such a clause:
[WHERE|ON] BackupDate = @BackupDate and BackupID = @BackupID

When deleting the records simply use this:

DECLARE @CutOffDate datetime DECLARE @BackupDays INT = 7; -- I's expect it to be a parameter of the SP SET CutOffDate = DATEADD(dd, - BackupDays, GETDATE())

DELETE FROM dbo.StatementData WHERE BackupDate < CutOffDate DELETE FROM dbo.ProofOfAccountData WHERE BackupDate < CutOffDate DELETE FROM dbo.PaidupLetterData WHERE BackupDate < CutOffDate

_____________
Code for TallyGenerator