mysql stored proc parameter

to start with: you should not have procedure with the same name as the table ...

apart from that, I don't really understand your explanation:

>so where i have  SET P_SEASON = 'H08'
>
>I want to have
> SET P_SEASON = ',SEAS,'
>
>and in the call, populate the fied to be season 'H09'

could you reiterate, please?

first point taken, Its a mock table at this point im just testing, but point taken.

To explain a bit more,
I'm using the above to format a table,



so where i have

  SET P_SEASON = 'H08'

I want to swap with the parameter (SEAS)

  SET P_SEASON = ',SEAS,'

I want to swap the static 'H08' in this example is a code for 'Hi Summer 08'

to  SET P_SEASON  (to equal the parameter)  ',SEAS,'

So, in the call

Call 'havais'('H09')   or what ever the season is im working on.

This selects me all my data formated as above, but I want to beable to dynamically add in the season code on every line.

Basically i just want to substitute  'H08' for my parameter, so when it runs, what ever i put in there is SET for P_SEASON.

not sure how else to explain it...

so, you want this?
still, I don't see where you use P_Season after that line?

delimiter $$
Drop procedure if exists `havais` $$
create procedure `havais`(SEAS varchar(20))

BEGIN

UPDATE havais
SET B_DESCRIPTION=replace(B_DESCRIPTION,E_SIZE,'');
    UPDATE havais
    SET A_STYLECODE =replace(A_STYLECODE,E_SIZE,'');
        UPDATE havais
        SET C_SHORT_COLOUR = left(D_FULL_COLOUR,5);
            UPDATE havais
            SET E_SIZE = left(E_SIZE,3);
                  UPDATE havais
                  SET M_SUBDEPARTMENT = L_DEPARTMENT;
            UPDATE havais
            SET P_SEASON = CONCAT(',', SEAS , ',') ;
        UPDATE havais
        SET S_VALID_MONTHS = "YYYYYYYYYYYY";
    UPDATE havais
    SET AD_RESERVED_B = '';

SET @selhav = concat("
select * from havais order by N_PRODUCT  ") ;

prepare hav from @selhav;
execute hav;
deallocate prepare hav ;


END $$
DELIMITER $$

call `havais`('H09');

Select allOpen in new window

Hi Angelll,

Close enough ;)
it was this..

( um im loading data infile, which as all barcode information for a brand, Im using this to format the columns, P_SEASON is column P in excel, its the season column, by putting this parameter in i can change all the season codes for the file to be correct.

Thanks


delimiter $$
Drop procedure if exists `havais` $$
create procedure `havais`(SEAS varchar(20))

BEGIN

UPDATE havais
SET B_DESCRIPTION=replace(B_DESCRIPTION,E_SIZE,'');
    UPDATE havais
    SET A_STYLECODE =replace(A_STYLECODE,E_SIZE,'');
        UPDATE havais
        SET C_SHORT_COLOUR = left(D_FULL_COLOUR,5);
            UPDATE havais
            SET E_SIZE = left(E_SIZE,3);
                  UPDATE havais
                  SET M_SUBDEPARTMENT = L_DEPARTMENT;
            UPDATE havais
            SET P_SEASON = CONCAT(',', SEAS , ',') ;
        UPDATE havais
        SET S_VALID_MONTHS = "YYYYYYYYYYYY";
    UPDATE havais
    SET AD_RESERVED_B = '';

SET @selhav = concat("
select * from havais order by N_PRODUCT  ") ;

prepare hav from @selhav;
execute hav;
deallocate prepare hav ;


END $$
DELIMITER $$

call `havais`('H09');
 

sorry the answer was this(as below)

Also do you know how to load data infile, in a stored procedure? or what everyone else does in this case?




delimiter $$
Drop procedure if exists `havais` $$
create procedure `havais`(SEAS varchar(20))

BEGIN

UPDATE havais
SET B_DESCRIPTION=replace(B_DESCRIPTION,E_SIZE,'');
    UPDATE havais
    SET A_STYLECODE =replace(A_STYLECODE,E_SIZE,'');
        UPDATE havais
        SET C_SHORT_COLOUR = left(D_FULL_COLOUR,5);
            UPDATE havais
            SET E_SIZE = left(E_SIZE,3);
                  UPDATE havais
                  SET M_SUBDEPARTMENT = L_DEPARTMENT;
            UPDATE havais
            SET P_SEASON = CONCAT('', SEAS ,'') ;
        UPDATE havais
        SET S_VALID_MONTHS = "YYYYYYYYYYYY";
    UPDATE havais
    SET AD_RESERVED_B = '';

SET @selhav = concat("
select * from havais order by N_PRODUCT  ") ;

prepare hav from @selhav;
execute hav;
deallocate prepare hav ;

>Also do you know how to load data infile, in a stored procedure?
you can use the LOAD DATA INFILE from a stored proc, from what I know ...
what is the issue you have?

I get the error

LOAD DATA is not allowed in stored procedures.  errorNr 1314.


I'm wanting to put this whole code into a single procedure,

1) creates the table
2) loads the data
3) formats the data
4) Outfiles the data back

any clues.

I can do these 4 steps manually, i was hoping to run them into 1 procedure..

#1

create table havais

(
A_STYLECODE           VARCHAR(55) default '',
B_DESCRIPTION         VARCHAR(255) default '',
C_SHORT_COLOUR        VARCHAR(50) default '',
D_FULL_COLOUR         VARCHAR(50) default '',
E_SIZE                VARCHAR(50) default '',
F_COST_AU$            VARCHAR(50) default '',
G_RETAIL_AU$          VARCHAR(50) default '',
H_COST_NZ$            VARCHAR(50) default '',
I_RETAIL_NZ$          VARCHAR(50) default '',
J_BARCODE1            VARCHAR(50) default '',
K_BARCODE2            VARCHAR(50) default '',
L_DEPARTMENT          VARCHAR(50) default '',
M_SUBDEPARTMENT       VARCHAR(50) default '',
N_PRODUCT             VARCHAR(50) default '',
O_CATEGORY            VARCHAR(50) default '',
P_SEASON              VARCHAR(50) default '',
Q_PACKID              VARCHAR(50) default '',
R_PACK_QUANTITY       VARCHAR(50) default '',
S_VALID_MONTHS        VARCHAR(520) default '',
T_ACCOUNT             VARCHAR(100) default '',
U_PURCHASE_ORDER      VARCHAR(120) default '',
V_DATE_ORDERED        VARCHAR(100) default '',
W_DELIVERY_DATE       VARCHAR(100) default '',
X_ORDER_QUANTITY      VARCHAR(200) default '',
Y_TAX_RATE            VARCHAR(60) default '',
Z_BUNDLE_QUANTITY     VARCHAR(30) default '',
AA_MINIMUM_ORDER      VARCHAR(30) default '',
AB_RESERVED_A         VARCHAR(30) default '',
AC_CATPAGE_NUMBER     VARCHAR(100)default '',
AD_RESERVED_B         VARCHAR(30) default '')

;

#2
Load data local infile
"J:/si/Havaianas/havitest.csv"
INTO TABLE havais
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

#3

delimiter $$
Drop procedure if exists `havais` $$
create procedure `havais`(SEAS varchar(20))

BEGIN

UPDATE havais
SET B_DESCRIPTION=replace(B_DESCRIPTION,E_SIZE,'');
    UPDATE havais
    SET A_STYLECODE =replace(A_STYLECODE,E_SIZE,'');
        UPDATE havais
        SET C_SHORT_COLOUR = left(D_FULL_COLOUR,5);
            UPDATE havais
            SET E_SIZE = left(E_SIZE,3);
                  UPDATE havais
                  SET M_SUBDEPARTMENT = L_DEPARTMENT;
            UPDATE havais
            SET P_SEASON = CONCAT('', SEAS ,'') ;
        UPDATE havais
        SET S_VALID_MONTHS = "YYYYYYYYYYYY";
    UPDATE havais
    SET AD_RESERVED_B = '*';

SET @selhav = concat("
select * from havais order by N_PRODUCT  ") ;

prepare hav from @selhav;
execute hav;
deallocate prepare hav ;


END $$
DELIMITER $$

# call `havais`('H09');  $$

#4

select * INTO OUTFILE
'/sqldata/havi/havistyl.txt'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
from havais;

Select allOpen in new window

Ahh Crap, thanks for this, i couldn't find this info, Still wonder what the worka round is>!