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?

asked 10 Jun '16, 11:54

Jongee's gravatar image

Jongee
17271117
accept rate: 0%

1

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...

(10 Jun '16, 18:04) Volker Barth

Thanks Volker, I will take a look.

(13 Jun '16, 06:51) Jongee

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;
permanent link

answered 10 Jun '16, 14:29

J%20Diaz's gravatar image

J Diaz
830253144
accept rate: 14%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×5

question asked: 10 Jun '16, 11:54

question was seen: 262 times

last updated: 13 Jun '16, 06:51