CODEWITHSUNDEEP
sql-serverloopsstored-proceduressql-update
Toby
Toby

Reputation: 145

SQL server execute SP from sql table and update

The table below stores sql insert statements and I run those from a sp. I need to also add an insert to the last_run_dt column. I put the code together via existing stackoverflow questions. I need help implementing this in my code, any feedback will be helpful.

How can I update my code to update the last_run_dt column?

Table:

audit_sql_id  audit_sql         last_run_dt
   1          select * from  <<need to add last run_dt value>>
   2          select * from  <<need to add last run_dt value>>

Code:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter proc [dbo].[sp_sqlAudit]
@packagename  as varchar(255)
as
begin

set nocount on;

if @packagename='SQL_DM_AUDIT'   

begin

declare @queries table (audit_sql_id int identity(1,1),sqlscript varchar(max))
declare @str_query varchar(max);
declare @startloop int
declare @endloop int

insert into @queries
select audit_sql 
from dw.dbo.audit_sql with(nolock)

select @endloop = max(audit_sql_id), @startloop = min(audit_sql_id)
from @queries

while @startloop < = @endloop
begin
    select @str_query = sqlscript 
    from @queries
    where audit_sql_id = @startloop

    exec (@str_query)

    set @startloop = @startloop + 1
end 
end
end

Upvotes: 0

Views: 37

Answers (1)

Stu
Stu

Reputation: 32609

I would suggest a slight refactor something like the below. There's no need to bring the entire list of sql statements into TemDB, just iterate over it and get each statement in turn. I would also always add a @debug parameter to print the sql instead if executing.

create or alter procedure dbo.sqlAudit
@packagename as varchar(255)
as
set nocount on;
declare @str_query varchar(max), @Id int
declare @AuditID table (Id int)

if @packagename='SQL_DM_AUDIT' 
begin
    insert into @AuditID (Id) /* Get list of IDs */
    select audit_sql_id
    from dw.dbo.audit_sql

    while exists(select * from @AuditID) /* Continue while there are IDs in the list */
    begin
        select top (1) @Id=Id from @AuditID /* Get an ID */

        select @str_query=audit_sql /* Get the sql for the ID */
        from dw.dbo.audit_sql
        where audit_sql_id=@Id

        delete from @AuditID where Id=@Id /* Remove this ID from the list */

        begin try
            exec (@str_query)
            if @@Error=0
            begin
                update dw.dbo.audit_sql set last_run_dt=GetDate() /* Update date for ID if run successful */
                where audit_sql_id=@Id
            end
        end try
        begin catch
            /*handle error*/
        end catch

    end
end

go

Upvotes: 1

Related Questions