Port MS SQL Cursor to Teradata

Database
Teradata Employee

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

1 REPLY
Junior Contributor

Re: Port MS SQL Cursor to Teradata

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;