• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2342
  • Last Modified:

Problem call ing MS SQL stored procedure, please help

I was trying to call a stored procedure in MS SQL server from a JSP page when this error occurs:

=========================================
SQLServer 2000 Driver for JDBC][SQLServer]Formal parameter '@account_name' was defined as OUTPUT but the actual parameter not declared OUTPUT.        
==========================================

This is how I call the sp form JSP page:

...........
cs = con.prepareCall("{call sp_account_search_cm(?,?,?,?)}");
      cs.setString(1, "50123365P1");
      cs.setString(2, "TRILOGY ADVISORS");
      cs.setInt(3, 25);
                cs.setString(4, "US");
      cs.registerOutParameter(1, java.sql.Types.VARCHAR);
      cs.executeQuery();
                                          
              while(rs.next())                                    {                                       out.println(cs.getString(1));                                                            
               }

  ....................

Is there anyting wrong with way I call the SP or I should look into the stored procedure itself?

Thanks

 

 
 
0
rdong
Asked:
rdong
  • 4
  • 2
2 Solutions
 
Kevin3NFCommented:
The error looks like it might be coming from the SP...please post that code

HTH,

Kevin3NF
0
 
rdongAuthor Commented:
Thanks, Kevin,

Here is the SP:

===========================
CREATE PROCEDURE sp_account_search_cm
@account_number varchar(500),
@account_name varchar(500),
@entity_type_cd int,
@domicile_coutry_cd varchar(50)
 AS
set @account_number='%'+ @account_number + '%'
set @account_name='%'+ @account_name + '%'
select A.account_id,P.party_name,
E.Entity_Type_Cd,E.Entity_Type_Desc,
P.Domicile_Country_cd,
C.country_name
from DRESDNER_PDS..Account A,DRESDNER_PDS..connected_parties CP,DRESDNER_PDS..Party P,DRESDNER_PDS..Entity E,DRESDNER_PDS..Country C
where A.account_id =CP.account_id and CP.party_Id=P.party_Id and P.entity_type_cd =E.Entity_Type_Cd and P.Domicile_Country_cd=C.country_Cd
--search string
and
((A.Account_Num LIKE @account_number) OR (P.party_name LIKE @account_name))
and
E.Entity_Type_Cd=@entity_type_cd
and
P.Domicile_Country_cd=@domicile_coutry_cd

GO

===========================
0
 
Kevin3NFCommented:
>>but the actual parameter not declared OUTPUT.<<
This is correct....you do not have an OUTPUT parameter defined in the stored proc.

I don't know JSP so I can't take you much further....
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
rdongAuthor Commented:
>>This is correct....you do not have an OUTPUT parameter defined in the  stored proc.

I do not know much about SP yet. I am calling someone's SP. So can you define an OUTPUT parameter in the SP? How?

0
 
Kevin3NFCommented:
From Books Online:

D. Use OUTPUT parameters
OUTPUT parameters allow an external procedure, a batch, or more than one Transact-SQL statements to access a value set during the procedure execution. In this example, a stored procedure (titles_sum) is created and allows one optional input parameter and one output parameter.

First, create the procedure:

USE pubs
GO

IF EXISTS(SELECT name FROM sysobjects
      WHERE name = 'titles_sum' AND type = 'P')
   DROP PROCEDURE titles_sum
GO

USE pubs
GO

CREATE PROCEDURE titles_sum
    @@TITLE varchar(40) = '%',
    @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE

SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO

Next, use the OUTPUT parameter with control-of-flow language.



Note  The OUTPUT variable must be defined during the table creation as well as during use of the variable.


The parameter name and variable name do not have to match; however, the data type and parameter positioning must match (unless @@SUM = variable is used).

DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
   PRINT ' '
   PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
   SELECT 'The total cost of these titles is $'
         + RTRIM(CAST(@@TOTALCOST AS varchar(20)))

Here is the result set:

Title Name                                                              
------------------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking

(3 row(s) affected)

Warning, null value eliminated from aggregate.
 
All of these titles can be purchased for less than $200.

0
 
Lori99Commented:
I don't know JSP either, but it looks to me like SQL Server thinks you are asking for an OUTPUT parameter because of this statement in your code.

cs.registerOutParameter(1, java.sql.Types.VARCHAR);

Unless it is required by JSP, I think if you simply remove it, your code will run.

What would you want returned in an output parameter?  It looks like your procedure is just returning a result set to you.  To define an output parameter in the procedure, you would need an additional parameter defined as OUTPUT like:

CREATE PROCEDURE sp_account_search_cm
@account_number varchar(500),
@account_name varchar(500),
@entity_type_cd int,
@domicile_coutry_cd varchar(50),
@my_output_param varchar(10) OUTPUT
 AS

proc code here

GO

If you are intending for your output parameter to be a return code for the success or failure of the stored procedure, that would be handled differently, I think, but I don't know what the JSP code to do that would be.

Hope this helps or at least points you in the right direction.
0
 
Kevin3NFCommented:
recommend split
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now