EXEC SQL with IN Clause

[Former Member]

Hello,

I'm trying to run an EXEC SQL statement with an IN clause. Here's my SQL code:

EXEC SQL.
      OPEN C FOR
      SELECT name
             FROM sv_hoover_data
             where dunsNum in :DUNS_NUMBERS
    ENDEXEC.

My DUNS_NUMBERS variable is of type String and contains the following data:

('12334223','4353434','54674563')

When the statement is executed, I receive the following error message in SM21:

Line 1: Incorrect syntax near '@P1'.

Also, the underlying database is MS SQL Server. When I run the query via MS Query Ananlyzer, it runs fine.

Does any know if EXEC SQL can handle IN clauses, and if so, how they're written?

Thanks,

Matt

[Sm1tje]

I haven't used it myself (yet), but this is what I found in the help:

EXEC SQL - EXECUTE

Syntax

EXEC SQL.

EXECUTE PROCEDURE proc ( IN p_in1 IN p_in2 ...,

OUT p_out1 OUT p_out2 ...,

INOUT p_inout1 INOUT p_inout2 ... )

ENDEXEC.

Effect

In database systems, you can define procedures as so-called "stored procedures". Since the syntax for calling such procedures and the pertinent parameter transfer for various database systems can vary widely, a uniform command exists in Native SQL.

The statement EXECUTE PROCEDURE calls a procedure proc stored in the database. For all formal parameters of the procedure, you must specify the actual parameters, separated by commas. You must specify IN, OUT or INOUT before every actual parameter, in order to indicate whether the parameter is an input, output, or input/output parameter. You can use literals or Host Variables labeled by a colon(:)for the actual parameters.

Example

This example defines a selfunc procedure using database specific SQL-Statements (Informix). It also calls the procedure using the SAP-specific Native-SQL-Statement EXECUTE PROCEDURE in a LOOP-loop by means of a Selection Table, and deletes the the procedure using an SQL-Statement. In the case shown here, the procedure is a function whose return value output in EXECUTE PROCEDURE is copied to the host variable name.

DATA scarr_carrid TYPE scarr-carrid.

SELECT-OPTIONS s_carrid FOR scarr_carrid NO INTERVALS.

DATA s_carrid_wa LIKE LINE OF s_carrid.

DATA name TYPE c LENGTH 20.

TRY.

EXEC SQL.

CREATE FUNCTION selfunc( input CHAR(3) )

RETURNING char(20);

DEFINE output char(20);

SELECT carrname

INTO output

FROM scarr

WHERE mandt = '000' AND

carrid = input;

RETURN output;

END FUNCTION;

ENDEXEC.

LOOP AT s_carrid INTO s_carrid_wa

WHERE sign = 'I' AND option = 'EQ'.

TRY.

EXEC SQL.

EXECUTE PROCEDURE selfunc( IN :s_carrid_wa-low,

OUT :name )

ENDEXEC.

WRITE: / s_carrid_wa-low, name.

CATCH cx_sy_native_sql_error.

MESSAGE `Error in procedure execution` TYPE 'I'.

ENDTRY.

ENDLOOP.

EXEC SQL.

DROP FUNCTION selfunc;

ENDEXEC.

CATCH cx_sy_native_sql_error.

MESSAGE `Error in procedure handling` TYPE 'I'.

ENDTRY.