Stored Procedure O/P as file

[Aditya Tiwari]

I have created a procedure which is giving me 300+ records.I want to store them in a file or in other words I need to craete a stored procedure whose o/p will be stored in a file(tab delimineted file).
I want to invoke them through a batch file.
Is it possible to do it ? Can anyone help me out in this matter.
I am giving my stored procedure for the reference.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE PROCEDURE client_core_select_duplicate ( OUT var0 VARCHAR(4000) )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- var0
------------------------------------------------------------------------
P1: BEGIN
-- Declare variable
DECLARE var0_TMP VARCHAR(4000) DEFAULT ' ';

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT
n.CLIENT_ID,
n.NAME_BUSINESS,
c.CORP_ID,
a.HOUSE_NBR,
a.ADDRESS1,
a.ADDRESS2,
a.ADDRESS3,
a.ADDRESS4,
a.CITY,
a.STATE_PROV_CD,
a.POSTAL_CODE
FROM
CLI_CLIENT c
JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID
LEFT OUTER JOIN CLI_CLNT_ADDR_V ca ON n.CLIENT_ID = ca.CLIENT_ID
LEFT OUTER JOIN CLI_ADDRESS_V a ON ca.ADDRESS_ID = a.ADDRESS_ID
WHERE
c.SEGMENTED_FLAG = 'N'
AND n.NAME_BUSINESS IS NOT NULL
ORDER BY
n.NAME_BUSINESS,
c.CORP_ID,
a.HOUSE_NBR,
a.ADDRESS1,
a.ADDRESS2,
a.ADDRESS3,
a.ADDRESS4,
a.CITY,
a.STATE_PROV_CD,
a.POSTAL_CODE,
n.CLIENT_ID;

-- Cursor left open for client application
OPEN cursor1;
SET var0 = var0_TMP;
END P1

[Mark A]

Post by Aditya Tiwari
I have created a procedure which is giving me 300+ records.I want to store
them in a file or in other words I need to craete a stored procedure whose
o/p will be stored in a file(tab delimineted file).
I want to invoke them through a batch file.
Is it possible to do it ? Can anyone help me out in this matter.
I am giving my stored procedure for the reference.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE client_core_select_duplicate ( OUT var0 VARCHAR(4000) )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- var0
------------------------------------------------------------------------
P1: BEGIN
-- Declare variable
DECLARE var0_TMP VARCHAR(4000) DEFAULT ' ';
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT
n.CLIENT_ID,
n.NAME_BUSINESS,
c.CORP_ID,
a.HOUSE_NBR,
a.ADDRESS1,
a.ADDRESS2,
a.ADDRESS3,
a.ADDRESS4,
a.CITY,
a.STATE_PROV_CD,
a.POSTAL_CODE
FROM
CLI_CLIENT c
JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID
LEFT OUTER JOIN CLI_CLNT_ADDR_V ca ON n.CLIENT_ID = ca.CLIENT_ID
LEFT OUTER JOIN CLI_ADDRESS_V a ON ca.ADDRESS_ID = a.ADDRESS_ID
WHERE
c.SEGMENTED_FLAG = 'N'
AND n.NAME_BUSINESS IS NOT NULL
ORDER BY
n.NAME_BUSINESS,
c.CORP_ID,
a.HOUSE_NBR,
a.ADDRESS1,
a.ADDRESS2,
a.ADDRESS3,
a.ADDRESS4,
a.CITY,
a.STATE_PROV_CD,
a.POSTAL_CODE,
n.CLIENT_ID;
-- Cursor left open for client application
OPEN cursor1;
SET var0 = var0_TMP;
END P1

Look at the ADMIN_CMD stored procedure if you have V8.1 FP9 or higher. You
can do an export and code your select statement directly into the export
command.