• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 676
  • Last Modified:

Sybase Assigning Variables in a Stored Procedure

I am trying to output the column names of a select statement. My thought was to assign the column names to a variable and then use that variable in my select statement and then output the column names from the same variable. I am getting errors when I try to assigh the field names to the variable. I have tried everything and I am stumped

alter procedure DBA.TryToGetUnload() /* parameters,... */
begin
  declare @ColumnNames varchar(200);
   select @ColumnNames  = id,first_name,last_name
  unload select @ColumnNames from customer to 'c:\\customer.txt' delimited by '~';
  end
0
Littlericky
Asked:
Littlericky
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have 1 variable, and several columns... that won't work like this

anyhow, why do you want variables, if you can unload without them?
http://dcx.sybase.com/1101en/dbreference_en11/unload-statement.html

please clarify
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note: if you wanted to assign several variables:

 declare @id int ;
 declare @lastname varchar(100); 
 declare @firstname  varchar(100);
   select @id = id, @firstname = first_name, @lastname = last_name
    from customers where id = 123;

as you can only have 1 value in the variables, not multiple rows at once

Open in new window

0
 
LittlerickyAuthor Commented:
The purpose of  this was to get an output file which contains not only the values from a select statement but also to get the column names which were used in that select statement.

The column names would be used when reading this information into a mysql db. We want to set up a function which used the column names to build the insert/update  ststement.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note sure how to save some data from sql into a file in general, but the UNLOAD won't put the column names ...

so, I would try this:
alter procedure DBA.TryToGetUnload() /* parameters,... */
begin
  unload select 'id,first_name,last_name'  to 'c:\\customer.txt' delimited by '~';
  unload select id,first_name,last_name from customer to 'c:\\customer.txt' delimited by '~' append on;
end

Open in new window

0
 
LittlerickyAuthor Commented:
For consistency/clarity/reduce errors I did want to get the field names into a variable. One of sybase limitation to the unload statement that is used in a procedure is that it can not append so I would end up doing something like this:

alter procedure DBA.TryToGetUnload() /* parameters,... */
begin
  unload select 'id,first_name,last_name' as TheFields, id,first_name,last_name from customer to 'c:\\customer.txt' delimited by '~' ;
end

Not the end of the world but I really would like to use a variable.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>but I really would like to use a variable.
believe me, I can understand fully
0
 
LittlerickyAuthor Commented:
There is no solution to my question
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now