| Newbie | | Join Date: Mar 2007
Posts: 5
| |
| re: Convert PostgreSQL DB to MS-SQL
Quote:
Originally Posted by ck9663 Post your stored proc and functions and we can try to help you convert it.Depending on the logic and business rules of your procedure and functions, it might not be able to be translated line by line. Instead, some (or all) of them might be completely re-written.
-- CK One of the Function: -
-- Function: sp_get_form_bag_content(bigint, bpchar)
-
-
-- DROP FUNCTION sp_get_form_bag_content(bigint, bpchar);
-
-
CREATE OR REPLACE FUNCTION sp_get_form_bag_content(in_cand_id bigint, in_payment_mode bpchar)
-
RETURNS SETOF typ_form_bag_content AS
-
$BODY$
-
DECLARE
-
-
v_form_id_arr _int2;
-
v_rec form_bag%ROWTYPE;
-
v_return_rec typ_form_bag_content;
-
v_index int2;
-
-
BEGIN
-
-
update cand_preferences
-
set payment_mode = in_payment_mode
-
where cand_id = in_cand_id
-
and payment_mode <> in_payment_mode;
-
-
v_index=0;
-
for v_rec in select form_id from form_bag where cand_id=in_cand_id
-
loop
-
v_form_id_arr[v_index]= v_rec.form_id;
-
v_index = v_index + 1;
-
raise notice '%', v_form_id_arr[0];
-
end loop;
-
-
-
for v_return_rec in
-
select temp2.form_id, college_name, course_list, form_cost, warning, sp_format_date(last_date,'DD-Mon-YYYY, DOW'), is_exam from (
-
select college_id, temp.form_id, course_list, form_cost, warning, last_date, is_exam from
-
(
-
select sp_warnings_cost.form_id, course_list, warning, form_cost from
-
(
-
select sp_cost.form_id, coalesce(sp_warnings.warning,'') as warning, sp_cost.form_cost from
-
(
-
select form_id,
-
array_to_string(array_accum(warnings), '::') as warning
-
from (
-
select *
-
from sp_check_form_constraints(in_cand_id, v_form_id_arr)
-
) as sp1_warnings
-
group by form_id
-
) as sp_warnings
-
-
right outer join sp_get_forms_cost(in_cand_id, v_form_id_arr) as sp_cost on sp_cost.form_id=sp_warnings.form_id
-
) as sp_warnings_cost
-
-
left outer join sp_get_form_bag_courses(in_cand_id) sp_courses on sp_warnings_cost.form_id=sp_courses.form_id
-
) as temp
-
inner join forms f on f.form_id=temp.form_id
-
)temp2
-
inner join colleges col on col.college_id=temp2.college_id
-
loop
-
-
return next v_return_rec;
-
-
end loop;
-
-
return;
-
END;
-
$BODY$
-
LANGUAGE 'plpgsql' VOLATILE
-
COST 100
-
ROWS 1000;
-
ALTER FUNCTION sp_get_form_bag_content(bigint, bpchar) OWNER TO postgres;
-
|
