How to Call a mysql stored process into SAS Viya 3.5

[harshpatel]

Hi,

How to call a sql stored procedure into SAS Viya 3.5?

I am running below code but it is throwing error.

PROC SQL;
Connect to odbc (datasrc="****"
user ="*****" password = "*******" );
create table t as
select * from connection to odbc
Execute(dbo.stp) by odbc;
Disconnect from odbc;
QUIT;

Error:

(Execute(dbo.stp)) by odbc;

------- -

79 22

76

ERROR 79-322: Expecting a SELECT.

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,

CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

[jklaverstijn]

Hi,

You are mixing up two SQL Pass-Through techniques: SELECT FROM and EXECUTE. Using EXECUTE is the correct way to call a stored procedure. But running a SELECT on top of that in a single query is syntactically unsupported. In fact you are mixing DDL and DML statements. Getting a result set with EXECUTE is not supported.

A work-around would be to have the STP create a result set and query that table after calling the STP, effectively splitting up the SELECT and EXECUTE statements.

Hope this helps,

-- Jan.