All Forums Database
JS186137 1 post Joined 10/15
19 May 2016
Port MS SQL Cursor to Teradata

Hello -
I have spent a good amount of time on my own searching the forums for the means to port a very simple MS SQL cursor to TD.
The purpose is to read through a metadata table record by record and pull a tablename and execute a count of rows on that table and so on...
Any assistance would be greatly appreciated.  I'm open to solutions that don't require a cursor as well.
 
MS SQL Example:
 
DECLARE @SAPtable varchar(1024)
DECLARE @SQLString varchar(8000)
DECLARE recon_Cursor CURSOR FOR
SELECT TableName FROM dbo.meta_recon_inputs
OPEN recon_Cursor
FETCH NEXT FROM recon_Cursor into @SAPtable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'SELECT COUNT(*) FROM dbo.'+@SAPtable+''
EXEC (@SQLString)
FETCH NEXT FROM recon_Cursor into @SAPtable
END
CLOSE recon_Cursor
DEALLOCATE recon_Cursor

Jason Stange Senior Pre-Sales Consultant | SAP CoE Americas
dnoeth 4628 posts Joined 11/04
20 May 2016

Most of the syntax can be converted easily (adding lots of semicolons), but returning the result of the counts is more complicated. You need an intermediate table to Insert the counts and then finally return a Select on this table:

REPLACE PROCEDURE CountProc()
DYNAMIC RESULT SETS 1
BEGIN
   DECLARE SAPtable VARCHAR(1024);
   DECLARE SQLstring VARCHAR(8000); 
   
   DECLARE recon_Cursor CURSOR FOR
   SELECT TableName FROM dbo.meta_recon_inputs;

   -- This is needed for returning the counts
   DECLARE rslt CURSOR WITH RETURN ONLY FOR s;
   -- used to store the counts
   CREATE VOLATILE TABLE VT_table_counts(
      TABLENAME VARCHAR(128), 
      CNT BIGINT
   ) ON COMMIT PRESERVE ROWS; 
   
   OPEN recon_Cursor;
      FETCH NEXT FROM recon_cursor INTO SAPtable;
       
      WHILE SQLCODE=0 DO 
         SET SQLstring = 'INSERT INTO VT_table_counts SELECT '''|| saptable ||''', COUNT(*) FROM dbo.' || SAPtable || ';';
         EXECUTE IMMEDIATE SQLstring;
         FETCH NEXT FROM recon_cursor INTO saptable; 
      END WHILE; 
   CLOSE recon_cursor; 

   -- now return the counts
   SET SQLstring = 'SELECT * FROM VT_table_counts;';
   PREPARE s FROM SQLstring;
   OPEN rslt;
   DROP TABLE VT_table_counts;
   
END;

 

Dieter

You must sign in to leave a comment.