SAP SQL Anywhere

Question

Hi,

I am still getting to grips with SQLA/ISQL so I know this might be a rookie error, but any help would be great.

Below is my code

GO
CREATE PROCEDURE dba.sp_product_export @prod varchar(25)

AS

DECLARE @sql LONG VARCHAR;

BEGIN

    DECLARE @output_filename varchar(100);

    SET @output_filename = 'C:\TEST\' || @prod || '-' || DATEFORMAT(CURRENT TIMESTAMP, 'HHNNSSSS-DD-MM-YYYY') || '.txt';

UNLOAD 
    SELECT prodref, proddesc, memo, created, createdby, lastupdated, lastupdatedby
    FROM dba.proditem
    WHERE prodref = @prod
    TO @output_filename FORMAT ASCII;

END
GO

The error I am getting is @prod cannot be found. Now I understand that I am not declaring a variable at this point, but if I run the following command

exec dba.sp_product_export @prod = '8400005'

It executes with no errors, but no file is output.

Any suggestions?

Accepted Answer

Looks like a cross between TSQL and ANSI SQL try this although I haven't tested it...

CREATE PROCEDURE dba.sp_product_export(
  IN @prod VARCHAR(25)
  )
BEGIN
  DECLARE @sql LONG VARCHAR;
  DECLARE @output_filename varchar(100);

  -- SET @output_filename = 'C:\TEST\' || @prod || '-' || DATEFORMAT(CURRENT TIMESTAMP, 'HHNNSSSS-DD-MM-YYYY') || '.txt';
  SET @output_filename = STRING( 'C:\TEST\', @prod, '-', DATEFORMAT(CURRENT TIMESTAMP, 'HHNNSSSS-DD-MM-YYYY'), '.txt');

  UNLOAD 
    SELECT prodref, proddesc, memo, created, createdby, lastupdated, lastupdatedby
      FROM dba.proditem
      WHERE prodref = @prod
    TO @output_filename FORMAT ASCII;

END;

SAP SQL Anywhere

Error with Stored Procedure not recognising variable

Follow this question

Related questions