The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
1183416
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
830243044
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:

×100
×5

question asked: 10 Jun '16, 11:54

question was seen: 171 times

last updated: 13 Jun '16, 06:51