Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008
    Posts
    4

    Red face Unanswered: help to convert stored proc from ingres to mssql

    hi all,

    I have an ingres stored proc which needs to be converted into a mssql stored procedure. I am following a part of it, the only thing I am confused is with the Result Row please tell me what is Result Row. can anyone help me converting this proc to mssql stored proc?

    CREATE PROCEDURE d_member_search
    (
    l_card_no = varchar(20) with null,
    l_member_forename = varchar(30) with null,
    l_member_surname = varchar(30) with null,
    )
    result row
    (
    varchar(400),
    integer4, varchar(20), char(10), varchar(15), varchar(30),
    varchar(15), varchar(30),
    varchar(40), char(40), char(8), char(1), varchar(30),
    varchar(30), varchar(20), char(3), varchar(50), char(3),
    integer4, char(16), char(40), varchar(96), char(96),
    char(20), char(20), char(2), char(2), char(2),
    char(2), char(10), char(10)
    )
    AS

    DECLARE

    begin

    for

    SELECT member_no, card_no, title,
    forename, surname, sort_forename,
    sort_surname, display_name, job_title,
    dob, sex, address1,
    city, postcode, country,
    display_address, nationality, company_no,
    phone1, email, pref_comms_address,
    display_comms_address, id_card_no, passport_no,
    status_code, member_type_code, tier_level_code,
    language_code, hotel_id, status
    INTO :member_no, :card_no, :title,
    :forename, :surname, :sort_forename,
    :sort_surname, :display_name, :job_title,
    :dob, :sex, :address1,
    :city, ostcode, :country,
    :display_address, :nationality, :company_no,
    hone1, :email, ref_comms_address,
    :display_comms_address, :id_card_no, assport_no,
    :status_code, :member_type_code, :tier_level_code,
    :language_code, :hotel_id, :status
    FROM member_dupkey
    WHERE card_no IN (:l_member_no_1, :l_member_no_2, :l_member_no_3,
    :l_member_no_4, :l_member_no_5, :l_member_no_6,
    :l_member_no_7, :l_member_no_8, :l_member_no_9,
    :l_member_no_10 )
    AND member_type_code <> 'G'
    ORDER BY sort_surname, sort_forename

    do

    return row (
    :l_col_list,
    :member_no, :card_no, :title,
    :forename, :surname, :sort_forename,
    :sort_surname, :display_name, :job_title,
    :dob, :sex, :address1,
    :city, ostcode, :country,
    :display_address, :nationality, :company_no,
    hone1, :email, ref_comms_address,
    :display_comms_address, :id_card_no, assport_no,
    :status_code, :member_type_code, :tier_level_code,
    :language_code, :hotel_id, :status )

    endfor ;

    return 0;

    end
    need urgent help!!

    Thanks

    Zeeshan

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I can only assume that the result row is what the proc returns...

    Your sproc can easily return a resultset, just plonk a SELECT statement in there
    Code:
    CREATE PROC dbo.a
    AS
      BEGIN
        SELECT 'oh look a result' As col1
      END
    George
    Home | Blog

Posting Permissions

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