Dynamics AX 2012 R2 Execute Stored Procedure with Parameters
Created at 2015-03-12
Updated at 2018-05-01
Tag
Microsoft Dynamics AX
转自:http://tech.alirazazaidi.com/execute-stored-procedure-with-parameters-dynamics-ax-2012/
During in one of my assignment, I have to call sql server stored procedure from X++ code. There is little tricky to call stored procedure with parameters. Following code will help me to call stored procedure with Parameters. Consider sp_StudentCreates a stored procedure with three parameters, first two string and last one is date.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 private void InsertStudent( StudentDC _dc) { LoginProperty loginProperty; OdbcConnection odbcConnection; Statement statement; ResultSet resultSet; LoginProperty myLoginProperty; str sql, criteria; int output; SqlStatementExecutePermission perm; str myUserName="dynamicworld\aliraza.zaidi"; str myPassword="xyz"; str myConnectionString; myConnectionString=strfmt("UID=%1;PWD=%2",myUserName,myPassword); myLoginProperty = new LoginProperty(); myLoginProperty.setServer("dynamicworld.com"); myLoginProperty.setDatabase("studentDb"); myLoginProperty.setOther(myConnectionString); //Create a connection to external database. odbcConnection = new OdbcConnection(myLoginProperty); if (odbcConnection) { sql = "Execute sp_StudentCreates '" +_dc.parmFirstName())+"','" +_dc.parmLastMame())+"','" +date2str(_dc.parmDate(), 321,DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4)+"'"; info(sql); //Assert permission for executing the sql string. perm = new SqlStatementExecutePermission(sql); perm.assert(); //Prepare the sql statement. statement = odbcConnection.createStatement(); output = statement.executeUpdate(sql); statement.close(); } else { error("Failed to log on to the database through ODBC."); } }
