What is the equivalent of a TeraData PREPARE statement in SQL Server

Question

I am in the situation where I am converting a big tera data stored procedure to SQL Server. I have got through most of it, but the below code has stumped me. I am also trying to find online help on this, but apart from the description, I haven't been able to see a working example of how the PREPARE statement behaves in conjunction with a Cursor. (I really dont know much about TeraData and it has been only 2 weeks since I started scratching its surface)

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;

Can someone please help me translate this code to a more T-SQL'ised form?

Answer 1

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.