Hi all,

I need some help from you experts on this stored procedure..

There are 4-5 batch procedures that will call this stored procedure. The batch will call the stored procedure with 'I' parameter in the beginning of the batch process. The batch will call the stored procedure with 'O' parameter at the end of the batch process. The stored procedure should write the details into the auditing table.

Example record in acfg table:
Code:
ID	Mand	Bcode	Skey	I_O	Tname	Cname					Func	 Group_col1	   WhereSQL		  Message
1	M1	P1	1	O	Table1	COUNTRY					COUNT	   COUNTRY					%1 entries found for  %2
2	M1	P1	2	O	Table1	FIRSTNAME			   COUNT							   %1 total lines
3	M1	P2	1	I	Table1	FIRSTNAME			COUNT								%1 lines found
The logic of the stored procedure for the example is:

*1*. If acfg.v_yn is 'Y'

Read the record in acfg table using mand=pi_mand and bcode = pi_bcode order by skey;

*a*. if I_O parameter is 'I' then:

Fetch the records from step 1).
Generate a dynamic SQL like select func(cname) from tname where mand= pi_mand; /* sql should also include acfg.wheresql in the
where clause, sql should also include acfg.group_col1,acfg.group_col2, acfg.group_col3 if there are any values. */

Executing this above sql calculates count(firstname) from table1 where mand='M1';

Fetch the output of the above query and using the acfg.message field for that row '%1 lines found' insert/update this message into auditing.message as '32 lines
found'

Assign skey for the auditing table, based on the auditing.ccode.

*b*. if I_O parameter is 'O' then:

Fetch the records from step 1).
Generate a dynamic SQL like select func(cname) from tname where mand= pi_mand and auditing.pid=pi_pid group by group_col1; /* pi_pid is the argument passed in
the SP. sql should also include acfg.wheresql in the where clause, sql should also include acfg.group_col2, acfg.group_col3 if there are any values. */

Executing this above sql calculates count(country) from table1 where mand='M1' and auditing.pid=12345 group by country;

Fetch the output of the above query and using the acfg.message field for that row '%1 entries found for %2' insert/update this message into auditing.message as
'20 entries found for Germany' '10 entries found for USA' '2 entries found for Denmark' /* can be more than 1 record */

Assign skey for the auditing table, based on the auditing.ccode for these 3 records.

The resulting record in the auditing table will look like:

Code:
ID	Cdate						   Ccode 		 Pid	Mand	Skey	Message
1	01.04.2009 08:30:00			 P1		 12345	M1	1	20 entries found for Germany
2	01.04.2009 08:30:01			 P1		 12345	M1	2	10 entries found for USA
3	01.04.2009 08:30:02			 P1		 12345	M1	3	2  entries found for Denmark
4	01.04.2009 08:30:03			 P1		  12345	M1	4	32 total lines
5	01.04.2009 08:31:00			 P2		 12346	M1	1	32 lines found

I would appreciate any kind of help with this... Please help me.. Thanks a lot