Post reply

How to return multiple recordsusing Stored Procedure?

  • May 5, 2008 at 10:16 am

    #181902

    Hi how to return multiple records from sql server without using WHERE clause.

    In Oracle using CURSOR i did it.

    Pls send the SP whic returns multiple records.

    Regards

    Sharma.

  • May 5, 2008 at 10:30 am

    #810798

    In SQL also its possible to use cursor...

    Can you please explain your query with example ?

    🙂

  • May 5, 2008 at 10:36 am

    #810802

    Thanks for ur reply..

    My query is..

    Select id,name,address,phoneno,email,salary from contacts;

    The above query returns more than 100 records.

    I have to call this SP in JSP and display the same.

    Regards

  • May 5, 2008 at 11:01 am

    #810810

    CREATE PROCEDURE usp_ReturnRecords

    AS

    BEGIN

    SELECT

    id,

    name,

    address,

    phoneno,

    email,

    salary,

    FROM

    contacts

    END

    Then when calling the stored proc use:

    EXEC usp_ReturnRecords

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • May 5, 2008 at 10:46 pm

    #811007

    Thanks..

    It is worked in sql server.

    How can i call this from JSP?

    How to specify parameters?

    Regards

  • May 6, 2008 at 7:20 am

    #811180

    Sharma

    Unfortunately, I don't know JSP, I'm a ASP/.NET developer. However, I did do a google search and the following url appeared to have very good information on working with database objects from JSP

    http://www.java2s.com/Code/Java/JSP/CallingaStoredprocedurewithinaJSP.htm

    Hope this helps

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • May 7, 2008 at 7:10 am

    #811742

    Thanks..

    Thanks for ur help.

    That link contain sp which call oracle .

    In oracle using CURSOR i did it.

    In SQL Server SP no cursor is used in that procedure. How can i get it from jsp?

  • May 7, 2008 at 8:12 am

    #811813

    in ASP the data is returned in a recordset, and then we code against that recordset. Again, I'm not sure how JSP uses data returned from the database. However, example 13 on that link I sent shows how to request data and then put it in a result set. The snipit is as follows:

    Please enter the ID of the publisher you want to find:

    <%

    Connection connection = DriverManager.getConnection(

    "jdbc:odbc:data", "userName", "password");

    Statement statement = connection.createStatement();

    String id = request.getParameter("id");

    ResultSet resultset =

    statement.executeQuery("select * from tableName where id = '" + id + "'") ;

    if(!resultset.next()) {

    out.println("Sorry, could not find that publisher. " +

    "Please .");

    } else {

    %>

    <%

    }

    %>

    The important part I believe is this subsection of the snipet

    ResultSet resultset =

    statement.executeQuery("select * from tableName where id = '" + id + "'") ;

    I believe you would replace the select statement a call to the stored proc which places the data in the resultset.

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • May 7, 2008 at 10:42 am

    #811942

    Thanks for ur reply..

    In SP where the retrived values are stored?

    If i am calling the SP means it throws invalid binding exception..

    In Oracle, if we want to retrun more than one record and calling from JSP means it is must to use CURSOR..

    I am not sure abt in SQL Server SP with calling in JSP .

    Regards

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply