PREPARE Statement in conjunction with Cursor

Database
Enthusiast

PREPARE Statement in conjunction with Cursor

Hello all,

This is my first post on the TeraData developer forum. So I apologize in advance if my question has already been asked earlier or I did not follow any convention.

I am migrating a TeraData stored procedure to SQL Server 2012. I really dont know anything about TeraData and its only for this specific project that I am using it. 

I have the following piece of code that I really dont understand and I am looking for some help in translating it to a T-SQL version. 

              SET cur_stmt1=v_select_stmt||v_where_stmt;

              PREPARE s1 FROM cur_stmt1; 

              OPEN cur1;

                           SET v_record_cnt = Activity_Count;

                           IF(v_record_cnt=0) THEN

                           close cur1;

                           SET cur_stmt1='select 0 as cnt1,' || v_col_null_stmt;

                           PREPARE s1 FROM cur_stmt1; 

                         OPEN cur1;

                           end if;

What does the above mean? What is the role of a PREPARE and a cursor statement in above. Any other way of writing this? Thanks.

Tags (3)
3 REPLIES
Junior Contributor

Re: PREPARE Statement in conjunction with Cursor

To return the results of a SELECT you need to use a cursor in Teradata (and Standard SQL), of course this is not a real cursor where you fetch one row after the other, it's just a spool like any other result set.


In your case there's a SELECT using Dynamic SQL, so this should translate to T-SQL as

    EXEC sp_executesql @cur_stmt1;

And then it's simply checking if the answer set is empty and returns a single row answer set with that `select 0 ...` instead.

Enthusiast

Re: PREPARE Statement in conjunction with Cursor

This question has been answered by dnoeth on Stackoverflow. Anyone needing an answer, here's the link:

http://stackoverflow.com/questions/26551978/what-is-the-equivalent-of-a-teradata-prepare-statement-i...

Enthusiast

Re: PREPARE Statement in conjunction with Cursor

Thank you dnoeth! I really appreciate your help.