Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2013
    Posts
    8

    Unanswered: null check in Stored Procedure cursor

    Hi,

    Can anybody help me to give the right stored procedure syntax for the below code to check whether the cursor returned is null or not. If it is null, I need to fetch another table to check for the data existence.

    create procedure fetchAccountsTable
    @param1 char(1)
    as

  2. #2
    Join Date
    Dec 2013
    Posts
    8

    null check on stored procedure cursor

    Hi,

    Can any body help me to provide the right syntax to perform the null check on the cursor. Please check the below code snippet where I need to check the cursor returned null or not, and if it is null, I need to hit another table and to open the new cursor. If the cursor is not null I need to return the same.

    create procedure dbo.fetchAccountsTable
    @param1 char(1)
    as
    begin
    declare mycur CURSOR for select * from students where name=@param

    //If mycur is null , I need to hit another table department and to return the result set
    //If mycur is not null, I need to return the cursor mycur

    Can you please provide me the syntax for the above issue..Thanks in advance.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You've posted the same question in two forums, so it would help to know what server product you are actually using... That will make a difference in the solution.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You've posted the same question in two forums, so it would help to know what server product you are actually using... That will make a difference in the solution.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2013
    Posts
    8
    Actually the post got submitted by mistake before completing the question..

    sorry for the inconvenience.

  6. #6
    Join Date
    Dec 2013
    Posts
    8
    Hi Pat,

    Thanks for your quick response.

    I am using sybase data server..Hope this will help you..!!

    Thanks

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    threads merged, there was more detail in the MySQL thread, which has been merged with the one here in the Sybase forum
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2013
    Posts
    8
    Thanks healdem..!

    but sorry to say that I couldn't find out the link which you pointed out..could you please provide me the link again..?

  9. #9
    Join Date
    Dec 2013
    Posts
    8
    Hi,

    Please find below my code.. I want to use the cursor instead of getting the count and checking the count value to hit the table for another conidtion.

    Also I am using java code to execute this sybase stored procedure. Can anybody please help me to use the cursor in the below code and return the result set for the select statements.

    Appreciate your help.. and I am new to stored procedure and really stuck on this...

    CREATE PROCEDURE dbo.fetchStudentData
    @p_reg_no char(1),
    @p_dept_code char(1),
    @p_branch_cd varchar(3),
    @p_acct_base varchar(8),
    @p_name varchar(8)

    AS
    BEGIN
    DECLARE @busy_loading char(1)
    DECLARE @count_records int

    SELECT @busy_loading = busy_loading
    FROM tempdb..system_dates
    WHERE date_code = 'BUSINESS' AND reg_no = @p_reg_no

    IF(@busy_loading='N') AND (@p_reg_no='111')
    BEGIN
    SELECT @count_records=count(*) FROM database1..student WHERE branch_cd=@p_branch_cd AND acct_base=@p_acct_base AND deptcode=@p_dept_code

    IF(@count_records = 0 )
    BEGIN
    SELECT * FROM database1..student WHERE name=@p_name ORDER BY ind_value
    END
    ELSE
    BEGIN
    SELECT * FROM database1..student WHERE branch_cd=@p_branch_cd AND acct_base=@p_acct_base AND deptcode=@p_dept_code
    END
    END
    ELSE IF (@busy_loading='N') AND (@p_reg_no='222')
    BEGIN
    SELECT @count_records=count(*) FROM database2..student WHERE branch_cd=@p_branch_cd AND acct_base=@p_acct_base AND deptcode=@p_dept_code

    IF(@count_records = 0)
    BEGIN
    SELECT * FROM database2..student WHERE name=@p_name ORDER BY ind_value
    END
    ELSE
    BEGIN
    SELECT * FROM database2..student WHERE branch_cd=@p_branch_cd AND acct_base=@p_acct_base AND deptcode=@p_dept_code
    END
    END
    ELSE IF (@busy_loading='N') AND (@p_reg_no='333')
    BEGIN
    SELECT @count_records=count(*) FROM database3..student WHERE branch_cd=@p_branch_cd AND acct_base=@p_acct_base AND deptcode=@p_dept_code

    IF(@count_records = 0)
    BEGIN
    SELECT * FROM database3..student WHERE name=@p_name ORDER BY ind_value
    END
    ELSE
    BEGIN
    SELECT * FROM database3..student WHERE branch_cd=@p_branch_cd AND acct_base=@p_acct_base AND deptcode=@p_dept_code
    END
    END
    END
    GO

  10. #10
    Join Date
    Dec 2013
    Posts
    14
    Assuming you're using Sybase ASE (Adaptive Server Enterprise), there are several different ways to code something like this; with the final coding design relying on a combination of a) performance issues, b) coding complexity and c) how you code your front end application.

    NOTE: I have no idea why you want to use a cursor.

    A first pass might look like:

    =======================================
    CREATE PROCEDURE dbo.fetchStudentData
    @p_reg_no char(1),
    @p_dept_code char(1),
    @p_branch_cd varchar(3),
    @p_acct_base varchar(8),
    @p_name varchar(8)

    AS
    BEGIN
    DECLARE @busy_loading char(1)
    DECLARE @count_records int

    SELECT @busy_loading = busy_loading
    FROM tempdb..system_dates
    WHERE date_code = 'BUSINESS' AND reg_no = @p_reg_no

    IF(@busy_loading='N')
    BEGIN

    if @p_reg_no='111'
    BEGIN
    SELECT * FROM database1..student WHERE branch_cd=@p_branch_cd AND acct_base=@p_acct_base AND deptcode=@p_dept_code
    if @@rowcount = 0
    SELECT * FROM database1..student WHERE name=@p_name ORDER BY ind_value
    END

    else

    if @p_reg_no='222'
    BEGIN
    SELECT * FROM database2..student WHERE branch_cd=@p_branch_cd AND acct_base=@p_acct_base AND deptcode=@p_dept_code
    if @@rowcount = 0
    SELECT * FROM database2..student WHERE name=@p_name ORDER BY ind_value
    END

    else

    if @p_reg_no='333'
    BEGIN
    SELECT * FROM database3..student WHERE branch_cd=@p_branch_cd AND acct_base=@p_acct_base AND deptcode=@p_dept_code
    if @@rowcount = 0
    SELECT * FROM database3..student WHERE name=@p_name ORDER BY ind_value
    END

    END

    END
    =======================================

    Your front-end application then just has to process 1 or 2 result sets (of the same structure).

    The repeated pair of 'SELECT * from databaseN' queries could be reduced to one set of SELECTs via the use of the exec immediate construct ('exec()'), but depending on how often you plan on calling this proc and your ASE version, the compilation overhead for using exec() could be cost prohibitive.

  11. #11
    Join Date
    Dec 2013
    Posts
    8
    Hi Mark,

    Thanks a lot for your valuable reply.

    I am using java code using Hibernate to call the stored procedure. When I try the code which I posted, I am getting one result set at a time using both java code and explicit execution..but when I try using the code which you posted I am getting two result set when I execute the program explicitly, but getting only one result in the java code.

    Also, when none of the select statements are executed, I am getting null pointer execution when the query.list() executes.. Do I need to return anything by default?? Please help

    //Java code
    Query query = session.getNamedQuery("fetchStudentData")
    .setParameter("regNo", "111")
    .setParameter("deptCode","ABC")
    .setParameter("branchCd", "200")
    .setParameter("acctBase", "1234")
    .setParameter("name", "TEST");

    query.list() // Null pointer exception when none of the select statments executes..

    //Mappping file
    <sql-query name="fetchStudentData" callable="true">
    <return alias="student" class="com.test.persistence.orm.Student"/>
    { call fetchStudentData(:regNo, :deptCode, :branchCd, :acctBase, :name)}
    </sql-query>

    Thanks in advance..!!

  12. #12
    Join Date
    Dec 2013
    Posts
    14
    Sorry, I don't program in java. With that in mind, generally the front-end is programmed like such:

    for each result set do ...
    for each row in a result set do ...
    end do (each row in a result set)
    end do (each result set)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •