x

How to modify a stored procedure

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
more ▼

asked Nov 28, 2012 at 02:05 PM in Default

avatar image

Chris322
0 1 1 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Do you mean ALTER or CREATE?

If you mean ALTER can you provide the code as it stands already so we can see what you are working with?

If it doesnt already exist then we are going to need some table definitions so we can give you something meaningful.

As a stab in the dark:

 create procedure YourProc
 as
 declare @AVar INT
 Select @AVar = YourNum FROM TheOtherTable

 DELETE FROM TheTable Where YourDateCol < @Var

 GO


more ▼

answered Nov 28, 2012 at 02:19 PM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1208
x479
x62

asked: Nov 28, 2012 at 02:05 PM

Seen: 912 times

Last Updated: Nov 28, 2012 at 06:45 PM

Copyright 2018 Redgate Software. Privacy Policy