SQL & how different databases implement it

I have noticed a lot of activity on my posts to do with ADODB SELECT statements in ASP VBScript code. So here is some more information, this time about executing stored procedures.

ORACLE STORED PROCEDURES

Let's run an Oracle stored procedure and extract the data from the OUT variable. This post does not teach you about Oracle PL/SQL programming, but the head of the stored procedure we are going to run is:
CREATE OR REPLACE PROCEDURE sp_create_device
(p_ingame_name IN VARCHAR2,
p_status IN VARCHAR2,
p_device_id OUT VARCHAR2
)
IS
.....bulk of stored procedure here....

The remainder of this post is about the ASP code you need to execute the above stored procedure. First just a bit of setup to create the ADODB objects (these can be put in an include file at the top of all your ASP scripts):
set objConn = server.CreateObject("ADODB.Connection")
objConn.Open (application("gc_db_conn"))
set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConn

In the above, replace application("gc_db_conn")which I have defined in my global.asa with the connection string for your database. More information setting up a connection string here. Now identify the stored procedure to execute:
objCommand.CommandText = "sp_create_device"
objCommand.CommandType = adCmdStoredProc

Next, for each of the stored procedure's variables as defined in the stored procedure itself (our procedure sp_create_device here has three variables, two of which are IN variables and one of which is an OUT variable), define the variables:
objCommand.parameters.append objCommand.createparameter("p_ingame_name",adVarChar,adParamInput, 20)
objCommand.parameters.append objCommand.createparameter("p_status", adVarChar,adParamInput, 25)
objCommand.parameters.append objCommand.createparameter("p_device_id", adVarChar,adParamOutput,24)

For the avoidance of doubt, I know the above will probably wrap on your screen, but there are THREE lines in the above bit of code, one for each parameter. Notice how each parameter is identified by name (the names are in the code taken from the stored procedure), the data type is defined, the direction (in or out) is defined and the length of the variable is specified.

Now let's assign a value to each of the input variables (there are only two IN variables, so only two of the parameters need to be given a value):
objCommand("p_ingame_name") = "ghast"
objCommand("p_status") = "forced offline"

Finally now execute the stored procedure and this allows us to return (extract) the result:
objCommand.Execute
v_device = objCommand("p_device_id")

The VBScript variable v_device now contains the value that the stored procedure put into its p_device_id variable.

Please note that no error processing / handling has been included in the above. But I know you know that!

Good luck. Feel free to drop me a line if you need any help.