How to write a store procedure for sybase IQ

[Former Member]

i am tryin gto write a store procedure for below given condition..

• IDownloaded – Checks an input trigger file name against the Header table to see if it exists and was previously downloaded/processed.

Please tell me how to write a store proc for the above given line.

Thanks,

Aparna

[former_member188490 · Accepted solution]

Here is a stored procedure based on my interpretation of your description.

Hope that helps you as a guideline writing your own procedures

Regards

------

Assuming

- header table ( headertab )
- ( headertab ) has columns ( FileName char(255) , processed char(1) ) recording filenames and their download status as Y or N
- sproc input param is ( TriggerFile char(255) )
- sproc should return
  0 - Not found
  1 - found but not downloaded
  2 - found and downloaded

------

drop table if exists headertab ;
create table headertab ( FileName varchar(255) , processed char(1) ) ;
insert headertab values ( 'file1' , 'Y' ) ;
insert headertab values ( 'file2' , 'N' ) ;

drop procedure if exists check_downloaded ;
CREATE PROCEDURE check_downloaded (IN TriggerFile CHAR(255) , OUT Processed INT)
BEGIN
        DECLARE err_notfound EXCEPTION
        FOR SQLSTATE '02000' ;

        DECLARE curThisFile CURSOR FOR
        SELECT processed from headertab where FileName = TriggerFile ;

        DECLARE ThisProcessed char(1) ;

        SET Processed = 0 ;

        OPEN curThisFile ;
        FileLoop:
        LOOP
                FETCH NEXT curThisFile INTO ThisProcessed ;
                IF SQLSTATE = err_notfound THEN
                        LEAVE FileLoop ;
                END IF ;

                IF ThisProcessed = 'Y' THEN
                        SET Processed = 1 ;
                ELSEIF ThisProcessed = 'N' THEN
                        SET Processed = 2 ;
                END IF ;

        END LOOP FileLoop ;
        CLOSE curThisFile ;
END
;
declare @processed char(1)
exec check_downloaded 'file0' , @processed
select @processed
;

[Former Member]

Thank you will try the same procedure for rest of them...