Error with Stored Procedure not recognising variable

[former_SQLA_member1694895]

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?

[VolkerBarth]

Just to add to Jim: As documented here, a procedure should be written either in Watcom-SQL or Transact-SQL, not in a mixture of both. Here, your procedure is mainly in Transact-SQL but the usage of statement delimiters (';') (and possible other syntax elements) is Watcom-SQL. AFAIK, mixing dialects often leads to strange error messages...

[former_SQLA_member1694895]

Thanks Volker, I will take a look.

[JimDiaz · Accepted solution]

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;