Create SQL store procedure

[Mahendra Darwatkar]

Hi All
How can I create Store Proecedure in SQL database
i.e. I am having one file of store procedure xyz.sql. I want to create "xyz" stored procedure in sql database and execure after words.
Can anybody help me in this...
rgds,
Mahendra

[Dave Soto]

You can CALL a stored procedure with JDBC, but you cannot CREATE one. Each DBMS has its own syntax for creating stored procedures, and the current JDBC spec does not handle this. You have to look into the syntax for the particular DBMS you are running.
Once you've created the stored procedure on the DBMS, calling it from a JDBC statement is pretty simple. (This example is VERY simplified, it assumes the stored procedure has no arguments)

CallableStatement cs = con.prepareCall("{call MY_STORED_PROC}");
ResultSet rs = cs.executeQuery();

The most important thing to notice is that it uses the {call ...} escape sequence. This instructs the JDBC driver to translate this sequence into the native command for your particular DBMS.