Database Stored Procedures

Recently I was asked about Flexsim's abilities to work with Stored Procedures in a database. I wasn't sure how to answer, so I did a little research into it and discovered how to call stored procedures for some different types of databases. The method for creating stored procedures is different for each type of database, as is the method for calling them. I'm not going to go into how to create them here, but I want to record how to call them using Flexsim's dbsqlquery() command.

For Access 2007, you use the EXECUTE command: (This might work with 2003, but I don't know. If someone can try it and let me know, that would be great.)

dbsqlquery("EXECUTE MyQuery"); // Calls a query with no parameters
dbsqlquery("EXECUTE MyOtherQuery 10"); // Calls a query with 1 parameter

For MySQL, you use the CALL command:

dbsqlquery("CALL MyQuery()"); // Calls a routine with no parameters
dbsqlquery("CALL MyOtherQuery(10)"); // Calls a routine with 1 parameter

For SQLServer, you can use the EXEC command:

dbsqlquery("EXEC MyQuery"); // Calls a stored procedure with no parameters
dbsqlquery("EXEC MyOtherQuery @param1 = 10"); // Calls a stored procedure with 1 parameter

NOTE: Don't call SELECT queries in a SQLServer stored procedure. They return tables with forward-only cursors and Flexsim doesn't support them (you'll see a "Fetch type out of range" error). However, you can call stored procedures that update tables and then you can call the SELECT query yourself.

I haven't wished for stored procedures too many times while building models, but once I figured this out, I thought I should share this with everyone so that we all know what can be done (and we can find the syntax for it later).