Call stored Procedure from external Microsoft SQL Server / Execute Query with Parameters

(0) Share

Report

Posted on by Roger M.

302

Hi,

I need to to call stored procedure from external SQL server from AX class. I use OdbcConnection to connect to a SQL Server and I can execute queries or updates with no problem.

The question is:

a) Can I execute queries like this "select fieldA,fieldb from mytable where fiedlA=? and fieldb=?" ?

b) Can I execute stored procedures without typing the native SQL code? something like executeStoredProcedure?

thank you

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (2)

Verified answer

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

The answer to both of the questions is YES. When you are using ODBC that mean you can use 100% pure SQL. You need to pass it as string.

Writing direct SQL should be avoided as much as possible, unless if there are no more alternatives for a problem. Dynamics AX Author also have used Direct SQL and store procedures at few places, but approach being used by AX author is good, compared to what normally developers follow. E.g. if you have your store procedure located on SQL server and because of any reasons that store procedure goes missing; every call from AX now, will get fail. You can do following as safest way.

1) Inside AX(X++) check every time, before running SP, whether SP exists on SQL server yet?

2) If answer to #1 is NO, create procedure in SQL by running code in X++

3) Run Store procedure by calling it from X++

It’s a broad subject, the use of ODBC or direct SQL execution inside AX, usually it is not recommended under normal circumstances.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (1)

Report

Note that if you need something more complex, you should consider better frameworks. For example, I published an example based on LINQ to SQL. (I use primarily ADO.NET Entity Framework, but it's quite similar and the point isn't that you should use this or that framework, but that you have a plenty of frameworks much better than OdbcConnection.)

Was this reply helpful? Yes No
Roger M. 302 on at

Like (0)

Report

The point is: I can execute SQL and stored procedures. Great! But I have no way to get the result from executing a stored procedure using something like this:

sql = "EXEC myprocedure"

Was this reply helpful? Yes No
Roger M. 302 on at

Like (0)

Report

I will take a look you your example. When you talk about use better frameworks it means they are available in AX or I need to include any dll to the system in order to use it?

Thank you

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

I suggest you click the link read the article, it should tell you how it works.

I'm not sure what's your definition of "including DLL to the system", but the answer probably is "no, you don't need that". I'm talking about standard .NET frameworks and AX already depends on .NET framework, so you don't have to install anything extra. You have to use a Visual Studio project, but you can simply add it to AOT and let AX deploy it; you don't have to manipulate any DLL files.

Was this reply helpful? Yes No

Community site session details

Call stored Procedure from external Microsoft SQL Server / Execute Query with Parameters

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Call stored Procedure from external Microsoft SQL Server / Execute Query with Parameters

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates