How to read number of rows using table variable

[Former Member]

Hi

I am trying to read a database table into a table variable.

For example,

v_employee = select * from Employee where name = 'John';

Now, requirement is to obtain number of rows returned by the query into table variable v_employee. How to get this rowcount?

I triend using cursors but now getting correct result.

[sagarjoshi · Accepted solution]

Hi,

I assume your question is how to find number of records into a scalar variable so that you can program on that result.

Look at example below from SQLScript guide.

CREATE PROCEDURE upsert_proc (IN v_isbn VARCHAR(20))

LANGUAGE SQLSCRIPT AS

found INT := 1;

BEGIN

SELECT count(*) INTO found FROM books WHERE isbn = :v_isbn;

IF :found = 0

THEN

INSERT INTO books

VALUES (:v_isbn, 'In-Memory Data Management', 1, 1,

'2011', 42.75, 'EUR');

ELSE

UPDATE books SET price = 42.75 WHERE isbn =:v_isbn;

END IF;

END;

[Former Member · Accepted solution]

Hello Sagar,

Thanks for the reply. You got it right. I had posted the same question on the Development Center forum and here is the link to reply from Richard for same question.

http://scn.sap.com/message/14007187#14007187

Just wanted to post it here as that reply as well provide the answer to question and with two different approaches.

Thanks! - AO.