CODEWITHSUNDEEP
sql-serversql-server-2008t-sqldynamic-sqlrowcount
Our Man in Bananas
Our Man in Bananas

Reputation: 5981

using @@ROWCOUNT in Dynamic SQL

I am using Dynamic SQL to retrieve datasets from multiple tables in order to monitor our daily data extraction from the iSeries system.

I have the below dynamic SQL code which works fine, but I want to only run the data to get each tables records if data has been extracted for the day

-- Create a table variable to store user data
DECLARE @myTable TABLE
(
    docID INT IDENTITY(1,1),
    docRef VARCHAR(50),
    letterDir VARCHAR(500)
);

insert @myTable select docRef, saveDir from alpsMaster.dbo.uConfigData

-- Get the number of rows in the looping table
DECLARE @RowCount INT, @SQL nvarchar(500), @LoopSQL nvarchar(2000), @Date varchar(20)

set @Date='29 Oct 2013'
SET @RowCount = (SELECT COUNT(docID) FROM @myTable) 

-- Declare an iterator
DECLARE @I INT

-- Initialize the iterator
SET @I = 1

-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
    BEGIN
    -- Declare variables to hold the data which we get after looping each record

    DECLARE @docRef VARCHAR(10), @saveDir VARCHAR(500)

    -- Get the data from table and set to variables
    SELECT @docRef = docref FROM @myTable WHERE docID = @I
    SELECT @saveDir = letterDir FROM @myTable WHERE docID = @I

    -- Display the looped data
    --PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I) + '; docRef = ' + @docRef 

select @LoopSQL='
use alpsProduction;
declare @SQL nvarchar(500);

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''[dbo].['+@docRef+']''))
    begin

    if exists(select * from sys.columns 
        where Name = ''YPTMPID'' and Object_ID = OBJECT_ID(''[dbo].['+@docRef+']''))
        begin

            set @SQL=''SELECT t.template_name,'''''+@saveDir+''''', Y.* 
             FROM [alpsProduction].[dbo].'+@docRef+' Y, alpsMaster.dbo.uDocumentTemplates t 
                where DTEINP='''''+@Date+''''' and t.template_Id=y.YPTMPID and t.docRef='''''+@docRef+'''''''


            exec sp_executesql @SQL

            end
    end
    '
    --print @LoopSQL
    exec sp_executesql @LoopSQL

    -- Increment the iterator
    SET @I = @I  + 1
END

so I tried using 

IF @@ROWCOUNT >0 
    Begin
        exec sp_executesql @SQL
    end

but it seems to never populate the @@Rowcount.

Whats the best way to only run that statement (exec sp_executesql @SQL) if the current table (identified by @docRef) has records in it for todays date (in the format dd mmm yyyy)

Upvotes: 0

Views: 2153

Answers (1)

The Hill Boy
The Hill Boy

Reputation: 162

Create job to execute a sql script in which u must check inserted data on current day then execute your sp. like this.

IF EXISTS ( SELECT * FROM @TABLE T WHERE DATEDIFF(DD, GETUTCDATE(), T.CREATEDON) = 0 )
BEGIN
     EXEC SP_EXECUTESQL @SQL
END

Upvotes: 1

Related Questions