default value of in parameter of stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

default value of in parameter of stored procedure

Author

Topic

vinayakshukre
Starting Member

11 Posts

Posted - 2009-01-01 : 23:44:47

My procedure is


create procedure deftest2
@in1 int =5,
@in2 int =4
as
	insert into dummydata values(@in1,@in2,20);

My java code to execute this proc is


String sCallQuery = "{ call deftest2 (,?)}";
CallableStatement cstmt= con.prepareCall(sCallQuery);		
cstmt.setInt(1,100);	
cstmt.execute();

I get following error


java.sql.SQLException: Incorrect syntax near ','.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
	at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:478)
	at ProcTest.main(ProcTest.java:152)

I am trying this on MS SQL with JTDS driver.
Here I was expecting it to consider value 100 for in2 and consider default 5 for in1.
Please let me know where I am going wrong.
Is there any document or tutorial which explains about use of default values through java ?

Similar thread in another forum is
http://www.daniweb.com/forums/post768508.html#post768508

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-01-02 : 02:50:41

In SQL, if you want to call that function and pass it only one parameter, it would be like this
EXEC deftest2 @in2 = 100

Because you're omitting the first parameter and only specifying the second, you have to specify it by name.
I don't know how that will translate into Java though. You will have to somehow name the parameter and pass it. Leaving nothing in the first parameter position will give an error.

--
Gail Shaw
SQL Server MVP