Workaround for SQL stored procedure to perform an error in the IDE or database

Recently, because some of the business in the project was written in a stored procedure, a simple stored procedure was added to the database as follows:

ALTER PROCEDURE [dbo]. [Test_addperson]  @name as varchar,  @password as varchar, @age as  int, @allCount int = 0 output--total asbegin insert INTO Z_pe Rson_test (Name,password,age) VALUES (@name, @password, @age) SET @allCount = @ @rowcountEND

This is a simple stored procedure that inserts data into the person table, and I'm not going to introduce the syntax, I'm sure you're all b! than my cow.

But when this statement runs in Navicat, the following error is reported

Must declare the scalar "XXXX", that is, you want to output data information, debugging for a long time, can not find where the error.

Finally, you have to add the entity class and service and DAO for person in your project, and then test with JUnit!

The following is only the IML code, the interface code is omitted here!

Personserviceimpl.java

@Transactional @service (value= "Personservice") public class Personserviceimpl implements personservice{@ Autowiredprivate Persondao persondao;public void Add (Person p) throws Exception {Persondao.add (P);}}

Personservicedaoimpl.java

@Repository (value= "Persondao") public class Persondaoimpl extends Sqlsessiondaosupport implements Persondao{public void Add (Person p) throws Exception {getsqlsession (). SelectOne ("Person.add", p); System.out.println ("------------" +p.getallcount ());  The Allcount variable here is defined in the person entity class, or the output data returned by the stored procedure is not taken. }catch (Exception e) {e.printstacktrace (); throw new Exception (E.getmessage ());}}}

Person-mapper.xml

<select id= "Add" statementtype= "callable" parametertype= "Java.util.HashMap" resulttype= "person" > <--here Label, If using a stored procedure make sure you use the Select Tag--><! [Cdata[{#{allcount,mode=out,jdbctype=integer}=call Test_addperson (#{name,mode=in,jdbctype=varchar},#{password, Mode=in,jdbctype=varchar},#{age,mode=in,jdbctype=integer},#{allcount,mode=out,jdbctype=integer})}]]></ Select>

Then run the code and find out that the stored procedure is no problem, and the added back data can also be added successfully

@Testpublic void Test2 () {ApplicationContext applicationcontext = new Classpathxmlapplicationcontext ("conf/spring-*. XML "); Personservice Personservice = (personservice) applicationcontext.getbean ("Personservice"); person person = new person ();p erson.setage;p erson.setname ("AAAA");p Erson.setpassword ("BBBB"); try { Personservice.add (person);} catch (Exception e) {//TODO auto-generated catch Blocke.printstacktrace ();}}

As can be seen from the above, some stored procedures in some IDE prompts the error, and does not fully represent that the stored procedure error, we may be some database operation software pits!

Workaround for SQL stored procedure to perform an error in the IDE or database