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

And why do you post this in a PostgreSQL forum then?
mssql (Microsoft SQL Server) is something completely different.

bcoz postgresql is of same company, so I thought ingres users maybe here as well.

No, Postgres and Ingres have nothing in common (any more), there might have been some similarities between them 20 years ago. But even the Ingres version I have worked with 15 years ago does not have anything in common with Postgres as far as I can remember.

But the important thing is that your target is none of the two.
You will need someone that has knowledge in Transact SQL.

Btw: what does bcoz mean?

There was never any common code between the two. (I believe that one of ingres' developers was also involved with PostgreSQL, later...)