Select Statement Stored Procedures in MS sql server not returning anything...

[Justin Fox]

ok,

here is my 'driver class' :

ResultSet rs = null;
SqlCommand command = new SqlCommand("Items_Select",con,CommandType.StoredProcedure());
command.Parameters.add(new SqlParameter("ItemNumber",1))
rs = command.fill();
while (rs.next()) 
{
System.out.println(rs.getString("ItemDescription") + " " + rs.getString("ItemNumber"));
}

the SqlComand.Fill() method:

.....

else if (this.type.equalsIgnoreCase(CommandType.StoredProcedure()))
      {
         CallableStatement cs = null;
              String Sproc = "{call "+StoredProcedureText+"(?";
              for(int i = 0; i<Parameters.size() -1;i++)
              {
                  Sproc += ",?";
              }
              Sproc += ")}";
              System.out.println(Sproc);
              try{
              cs = thisConnection.prepareCall(Sproc);
              for(int i = 0;i<Parameters.size();i++)
              {
                  cs.setObject(Parameters.get(i).getName(),Parameters.get(i).getObject());
              }
              rs = cs.executeQuery();
              }
              catch(Exception e){e.printStackTrace();}
      }
      return rs;

and finally, here is the stored procedure....

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Justin Fox
-- Create date: 01/15/09
-- Description: select items based on itemnumber (PK)
-- =============================================
ALTER PROCEDURE [dbo].[Items_Select] 
	-- Add the parameters for the stored procedure here
@ItemNumber varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

Select * from Items where ItemNumber = @ItemNumber
END

ok the problem is, when i take the 'where itemnumber = @Itemnumber' out of the
sproc, the resultset gets filled fine and I see it print, but if i uncomment/put the
'where itemnumber = @itemnumber' then the resultset doesn't get filled for some
reason...

Am I missing something?

Thanks,

Justin Fox>

[ujjwal soni]

Hi,

Are you able to call stored procedure directly without using the application ?

[Justin Fox]

I don't really understand what you mean. I can call the stored procedure from the MS Sql management studio in a query,
but I need to call it from the app.

And like I said before, if i take out the 'where itemnumber = @itemnumber' then the resultset gets filled if I leave it in there
it doesn't get filled.

its weird too because the parameter is always there, but when i use it, it doesn't work.

thanks,

Justin