question

I need to alter a stored procedure to check another table, return a number, calculate that number as # of days back, and delete all rows prior to that # of days back based off of a date. This is the stored procedure I am trying to modify. The table that it will be accessing will be storing an integer. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[PR_Insert_Log] @servername [sysname] = @@servername, @DBname [sysname], @Tablename [sysname], @Mess [varchar] (255), @Source [sysname] = Null, @Type [varchar] (255) = Null, @Number1 [int] = Null, @Number2 [int] = Null, @Date1 [dateTime] = Null, @Date2 [dateTime] = Null, @Text1 [varchar] (255) = Null, @Text2 [varchar] (255) = Null AS DECLARE @sqlstring nvarchar(4000) ,@RowSource INT ,@RowSourceID INT ,@MESS2 nvarchar(255) ,@MESS3 nvarchar(255) ,@SQLParam nvarchar(50) ,@TempTbl varchar(100) ,@LogID INT ,@out INT SET @TempTbl = '##' + @Source SET @sqlstring = N'IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N''[tempdb].[dbo].[' + @TempTbl + ']'') AND type in (N''U'')) DROP TABLE [tempdb].[dbo].[' + @TempTbl + '] CREATE TABLE ' + @TempTbl + ' (L_ID INT)' EXECUTE sp_executesql @sqlstring If @Source is not Null BEGIN EXECUTE [database].[dbo].[PR_Insert_RowSource] @Source ,@RowSourceID OUTPUT END --Create Insert and perform it. Set @sqlstring = 'INSERT INTO [database].[dbo].[T_database_DBA_Log_LOG] ([LOG_Server] ,[LOG_DBName] ,[LOG_Table] ,[LOG_Message] ,[LOG_Number1] ,[LOG_Number2] ,[LOG_Date1] ,[LOG_Date2] ,[LOG_Text1] ,[LOG_Text2] ,[LOG_Type] ,[LOG_SPID] ,[LOG_Person] ,[LOG_Source] ,[LOG_RowSource]) VALUES (''' + @servername + ''' ,''' + @DBName + ''' ,''' + @Tablename + ''' ,''' + @Mess + ''' ,' + Case WHEN @Number1 is null THEN 'NULL' ELSE Cast (@Number1 as varchar) END + ' ,' + Case WHEN @Number2 is null THEN 'NULL' ELSE Cast (@Number2 as varchar) END + ' ,' + Case WHEN @Date1 is null THEN 'NULL' ELSE '''' + Cast (@Date1 as varchar) + '''' END + ' ,' + Case WHEN @Date2 is null THEN 'NULL' ELSE '''' + CAST (@Date2 as varchar) + '''' END + ' ,' + Case WHEN @Text1 is null THEN 'NULL' ELSE '''' + @Text1 + '''' END + ' ,' + Case WHEN @Text2 is null THEN 'NULL' ELSE '''' + @Text2 + '''' END + ' ,' + Case WHEN @TYPE is null THEN 'NULL' ELSE '''' + @TYPE + '''' END + ' ,@@SPID ,''' + suser_sname() + ''' ,' + Case WHEN @Source is null THEN 'Null' ELSE '''' + @Source + '''' END + ' ,' + Case WHEN @RowSourceID is null THEN 'Null' ELSE cast(@RowSourceID as varchar) END + ') SELECT @out = SCOPE_IDENTITY()' EXECUTE SP_executesql @sqlstring, @params = N'@out INT OUTPUT', @out=@out OUTPUT SET @sqlstring = N'INSERT INTO ' + @TempTbl + ' VALUES(@LogID)' SET @SQLParam = N'@LogID INT' EXECUTE sp_executesql @sqlstring, @SQLParam, @LogID = @out IF @@error > 0 BEGIN PRINT 'Error: '+@sqlstring END