You can use the indexer to extract a single value or a The array has an indexer toĮxtract values.
DATA MERGE SAS DELETING RECORDS HOW TO
This example illustrates how to create row totals, row means, andįind the top month using a data step array. (I wrote this answer on and on that date, the following throws an error. My example stored procedure throws an error if you try to delete any rows within the past month. I also added some error checking logic in the stored procedure to show how you might prevent rogue deletes that are too aggressive due to the date being passed in. Check rows after calling the stored procedure
Check rows before calling the stored procedureĮXEC dbo.DeleteTableRowsLessThanSpecificDate = 'TestDelete' That should leave only one row in our example () Now, we're ready to test the stored procedure by deleting all rows Check this blog post about why you should use sp_executesql instead of EXEC()ĮXECUTE sp_executesql = let's test the stored procedure by passing in some different DeleteDates and checking the results. SET = 'DELETE FROM ' + + '.' + + ' WHERE DateOnTable < ''' + convert(VARCHAR(20), + '''' Build the command by concatenating the input TableName and input DeleteDate Declare a variable to hold the dynamic SQL commandĭECLARE NVARCHAR(4000) = sysname, sysname, date' RAISERROR ('Delete Date Too Recent - Delete cancelled',16,1) Example error checking to prevent deleting rows with a date within the last month WHERE id = object_id(N'.')ĪND OBJECTPROPERTY(id, N'IsProcedure') = 1ĭROP PROCEDURE. Now, let's create the 'delete' stored procedure.
Insert into TestDelete(ID, Name, DateOnTable) values (1,'NameOnTable','') IF OBJECT_ID('dbo.TestDelete', 'U') IS NOT NULLĬreate table TestDelete (ID int, Name varchar(100), DateOnTable Date) Create a test demo table and insert a couple of rows Create a stored procedure that builds a dynamic SQL statement based on your input parameters and then executes the commandįirst, let's create a test table with some data to play with.
0 kommentar(er)
