Update Not Working Within A Stored Procedure

Can A Stored Procedure In Db1 On My Pc CALL Stored Procedure In Db2 On Remote Pc?
i have two database, one is residing on remote pc pc1 and another is residing on another pc pc2, the deal is i want to call a stored procedure in db of pc1, which in turn calls another stored procedure in db of pc2. i knw it sound weird but it solve my problem.

---

Call A Stored Procedure From Another Stored Procedure
I have a stored procedure which selects based on a parameter (and has alot of "if's" and "concat's"), and I want to call it from another stored procedure which does the following

Select timesheet_time,
(IF(player = "P1", membdet, 0)) AS "Player1",
(IF(player = "P2", membdet, 0)) AS "Player2",
(IF(player = "P3", membdet, 0)) AS "Player3",
(IF(player = "P4", membdet, 0)) AS "Player4"
FROM timesheet
Group by timesheet_time

(membdet is a concatenated field from the first sp)

How do I call one stored proc from another?

---

Can't Edit A Stored Procedure/procedure Already Exists
mySQL server: 5.0.24A
client: Administrator: 1.2.3rc
It's a clean installation with no other versions of mySQL

I have no problems to create and run a stored procedure, but when i edit the stored procedure i get the error "MySQL error number 1304 PROCEDURE xxx already exists"

Anyone know what i am doing wrong?

---

Stored Procedure
I am trying to migrate sql from mssql server. Unfortunately, mysql seems to be very different.

Can someone show me how to convert following procedures for mysql?

create procedure sp_get_id as
Begin
declare @id int
begin transaction
select @id = isnull(max(id),0)+1
from id_table
update id_table
set id = @id
select id=@id
commit transaction
End
go
/* simply returns next id. id_table contains one record */

create proc sp_get_data @id int as
Begin
select id1 = c.id1, id2= c.id2, data_table = c.data_table into #filter1
from data_table c, data_table_cache k where ( (k.id=0 or k.id=@id) and c.id1 = k.ticker)

select id1 = c.id1, id2= c.id2, data_table = c.data_table into #filter2
from #filter1 c, data_table_cache k where ( (k.id=0 or k.id=@id) and c.id2= k.ticker)

select id1,id2,data_table from #filter2
End

---

Stored Procedure
Can someone help me with the syntax to my stored procedure? Every time
I try to run it I receive the error code 1064. I checked my syntax
against the documentation on mysql.com and it seems to be correct, but
of course I could be wrong. Here's my Code:

---

Stored Procedure
I'm trying to create a stored procedure for user login as follow

DELIMITER $$

DROP PROCEDURE IF EXISTS USER_LOGIN $$
CREATE PROCEDURE USER_LOGIN (
IN P_USERNAME VARCHAR(50),
IN P_PASSWORD VARCHAR(50)
)
BEGIN
SELECT U.USER_ID
FROM USERS U
WHERE U.USER_USERNAME = P_USERNAME
AND U.USER_PASSWORD = MD5(P_PASSWORD)
END $$

DELIMITER ;

when I try to complie the above code, it gives error. Could anyone point out my error, since I really don't know what's go wrong. The mysql version I'm using is 5.0.37.

---

Stored Procedure?
I am having trouble getting this procedure to properly enter the data into a table. It works when I do not add the @ToName info and only ask to insert the AccountID and FromName data. When i add the @ToNamevariable, no data is inserted.

I think it might have to do with the select statement. I have two TerritoryID, RegionID, DivisionID, and EmpID because I want this information to be entered for both the FromName and the ToName selected. Does anyone know what my problem is? Code:

---

Stored Procedure
I have a database and I am writing stored procedures for that. I have to insert a record into the database for which I have this procedure:

PROCEDURE `insert_price`(pc_id int, pc_level INT, price INT, from_date DATETIME, to_date DATETIME)BEGIN INSERT INTO price VALUES (pc_id, pc_level, price, from_date, to_date);
END

But I before doing this I have to check whether the pc_id column is unique and then finding the maximum pc_id and incrementing the value by one. This value then goes into the first argument of insert_price.

The sql scripts for these are pretty straightforward but I am having trouble converting these to procedures.

For example, I can find that pc_id is unique by executing show indexes in price;

And, also I can use the following select max(pc_id) + 1 from price;

But how do I get all this into a procedure and connect with the last Stored Pro?

---

Stored Procedure
I'm trying to create a stored procedure using the following syntax,
but I'm getting an error.

CREATE PROCEDURE `site`.`sp_ValidateInvite_SEL` (invitecode
varchar(10))
BEGIN

-- Check that the code exists (deleted/blocked users' invites will be
deleted)
IF NOT EXISTS (SELECT * FROM `invitations` WHERE `InvitationCode` =
invitecode) THEN
SELECT 'Error' As `Status`, 'Invitation Code is invalid.'
As `Details`;
ELSE
SELECT 'Invited' As `Status`, 'blahblah' As `Details`;
END IF

END

Could someone tell me what the problem could be? Can you use the EXIST
function as a condition (or a parameter within a EXIST function/
subquery)? I'm using MySQL Administrator which automatically add the delimiter
statement.

---

Stored Procedure
I am trying to convert a stored procedure written for sql to one that will work in mysql. I understand that I have to set the variables as IN for incoming variables, but I don't know what to do with the rest of the code. The following is the sql stored procedure: Code:

---

Stored Procedure
I ve got create an stored procedure and it work very well. But now i want to have a more bit difficult stored procedure. So i want to create a new procedure as this :

CREATE FUNCTION proUser(In nbr1 INT,OUT cur1 CURSOR)
BEGIN
DECLARE cur2 CURSOR FOR SELECT * FROM Users WHERE Users_Id = nbr1;
cur1 = cur2;
END

So i have an error on (In nbr1 INT,OUT cur1 CURSOR) ?????

Is it possible to realize a procedure returning cursor. Have you got some eg ???

---

Stored Procedure
I have a table for customers with various field, two of which are clientnumber and password.

I have a website where a user has to log in using their clientnumber and password and if it is correct it goes to another page.

What sort of stored procedure would I use to check that the user name and password are correct?

Would I have it return say true if it is true and then have an if else in the webpage that says that if it is true go to this page?

---

Stored PROCEDURE
Here is my sql statement:

DROP PROCEDURE IF EXISTS `check_user`;

CREATE PROCEDURE `check_user`(IN userid VARCHAR(25))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
select loginid from user where loginid=userid;
END;


I am using:

# Server version: 5.0.41-community-nt
# Protocol version: 10
# Server: localhost via TCP/IP
# User: root@localhost
# phpmyadmin

when i try to run above mentioned statement I get following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select loginid from user where loginid=userid' at line 6 .

---

Stored Procedure
i'm using MySql 5.0 and i wanna know how many Stored Procedures can i add to my DataBase, because until now only can create 2 SP. When i wanna create the third SP, it always delete SP created previously.

---

Stored Procedure
I created procedure which create user as well as give him all privileges..
-----------------------------
-----------------------------
DELIMITER $$

DROP PROCEDURE IF EXISTS `tsmdb`.`tsw_createuser_proc` $$
CREATE PROCEDURE `tsmdb`.`tsw_createuser_proc` (login char(10),pass char(10))
BEGIN
GRANT ALL PRIVILEGES ON *.* TO login@"%"
IDENTIFIED BY 'pass' WITH GRANT OPTION;
END $$

DELIMITER ;

CALL tsw_createuser_proc('Sansrati','sansrati')
-----------------------
------------------------
While executing it is creating user by parameter name (login) not by Sansrati.
What is the use of procedure if it is still static.

---

Stored Procedure
I'm facing a problem with stored procedures in MySQL 5.0.10.
My application is in ASP.NET and I'm using Corelab's MySQL library to transact with the database. The problem is that all stored procs return an error message "Can't return resultset in the given context." Calling the stored procs directly through the DBTools client interface also gives the same error.
I'm just not able to resolve this one. My client had given me the code and when I set it up on my local server I get this error and moreover, it is working fine at the client's site. Any idea why this is happening and a possible solution to this?

---

Stored Procedure
I just started using MySQL 5.0.3 and am playing around with stored procedures. I'm trying to find an example of calling a stored procedure from PHP. Any help pointing me in the right direction would be great.

---

Stored Procedure
I write a stored procedure as below but the like statement is always incorect.
What is the correct way for the like in select statement?
--------------------------------------------------------------------
CREATE PROCEDURE `usp_Product_Search`(IN varProductCode varchar(50))
BEGIN
IF varProductCode IS NOT NULL THEN
select * from tbproduct
where 1=1
and ProductCode LIKE varProductCode + '%';
End IF;

---

Stored Procedure
The Manual Reference says that I have to run the script fix_privilege_tables.sql, but I don´t have it. Only out extension(.sql).

---

Stored Procedure
I'm working on this database, and I used to put all of my queries in the php code itself and execute from there. Now, I've started moving everything into stored procedures, and just passing parameters.
So..is this bad? The benefits I see are that I dont have to find and replace code everywhere, I can just make a procedure and its instantly accessible everywhere.
Since I'm doing it this way, passing parameters, do i still need to worry about sql injection?

---

Stored Procedure
Im writing a asp.net webapp and I need some help with scheduling of a
stored procedure.
I'd like to know if it's possible to have a stored produdere (that I
have written) to be schedualed for execution. I'd like it to exec on
the mysql server on predetermined hours.

---

Stored Procedure
I'm connected remotely to a mysql-server as root. No problem, i can do everything (insert, update .. to all tables of all databases.).
But when i try to make a stored procedure, i get the error:
"access denied for user 'root'@'%' to the database reserv".
When i do it on the mysql-server itself, it works.

---

Stored Procedure
i have created a stored procedure for an assignment i haev, the procedure is called getschoolorder, this procedure worked i implemented it into thte database then somehow i didnt save a copy of it and i lost hte code for the procedure, i have to hand hte code in, is there anyway i can get the code with an sql querie or somethign im using oracle 9i,

---

Stored Procedure
i'm trying to create my first stored procedure.this is what i have so far. No procedural statements yet as I want to get this working first and then i'll add in more.
now i'm still getting errors on every line.what am I doing wrong?

DELIMITER //

CREATE PROCEDURE spinsertnew (_filesource int,_firstname char(200),_lastname char(200))

BEGIN

DECLARE _Random int;
DECLARE _Upper int;
DECLARE _Lower int;
DECLARE _MyIdentity int;
DECLARE _isdouble int;

END;
//

---

Stored Procedure Syntax
I'm used to the SQL Server world but am working with MYSQL but i can't seem to find the syntax through the documentation of MYSQL. I have this stored procedure, could somebody help me out on what i'm missing. Is it that i'm not specifying IN or OUT for the results? ...

---

How To Return A Value From A Stored Procedure?
In my stored procedure, I wrote an INSERT query and now I want to return the ID of newly inserted record.

I can get that ID by LAST_INSERT_ID() function. but how can I return this ID?

---

What's Wrong With This Stored Procedure?
I have this stored procedure
CREATE PROCEDURE `documents`.`insert_documents_by_id`(in_DocumentId integer, in_DocumentLanguage varchar(255))
BEGIN
IF in_DocumentId= -1 THEN
insert into document(language) values( in_DocumentLanguage );
select LAST_INSERT_ID();
ELSE
update document set language=in_DocumentLanguage where id=in_DocumentId;
select in_id;
END IF;
END $$
I keep getting the following error when I execute it
"No database selected"

What's wrong... the database is already selected and it's called documents

---

Problem In Stored Procedure Through C#.Net
i m facing a problem with my stored procedure,named "procPayment".
I am doing update or insert through this stored procedure depending on whether that empid exist in table Paymentmaster.if data exist in table "procPayment" will upadte the other details if not it will insert the details...
i wud like to get whether my syntax is fine or not'my stored procedure is like this :
DELIMITER $$;
DROP PROCEDURE IF EXISTS `jaldb`.`procPayment`$$
CREATE DEFINER=`jaldb`@`badda` PROCEDURE `procPayment`
(
in yr integer,in mnth integer,in varempid text,
p_conveyenceAllownce text,p_deputation text,p_Exgratia text,p_GPF text,p_HouseLoan text,
p_HRA text,p_HillDevAllowance text,p_MedicalAdvance text,p_MedicalReimb text,p_Other1 text,
p_Other2 text,p_OverTime text,p_Personal text,p_TABill text,p_TravelAdvance text,p_MedicalAllowance text,p_Basic text,p_Add text,p_AdditionalDA text,p_DA text,p_employeeid text,p_monthid text,p_special text,p_TotalPayment text,p_ToBank text
)
BEGIN
declare empidnew text;
declare paydate varchar(10);
declare tempempid text;
if mnth <10 then
set paydate = concat(&#3901;','/0',mnth,'/',yr);
else
set paydate = concat(&#3901;','/',mnth,'/',yr);
end if;
set tempempid = concat(&#390;', varempid);
set empidnew=(Select empid from paymentmaster where payrolldate=paydate and empid=varempid);
if (empidnew != null) then
update paymentmaster
set
Basic=p_Basic,
add50per=p_Add,
special=p_special,
conveyanceallowance=p_conveyenceAllownce,
deputation=p_deputation,
personal=p_Personal,
DA=p_DA,
additionalDA=p_AdditionalDA,
HRA=p_HRA,
hilldevAllowance=p_HillDevAllowance,
overtime=p_OverTime,
MedicalAdvance=p_MedicalAdvance,
GPFAdvance=p_GPF,
medicalallowance=p_MedicalAllowance,
exgratia=p_Exgratia,
houseloan=p_HouseLoan,
medicalreimb=p_MedicalReimb,
traveladvance=p_TravelAdvance,
TABill=p_TABill,
other1=p_Other1,
other2=p_Other2,
totalpayment=p_TotalPayment,
tobank=p_ToBank
where empid=varempid;
ELSE
nsert into paymentmaster
(
empid,payrollDate, Basic, Add50per, Special,ConveyanceAllowance,
Deputation,Personal,DA,AdditionalDA,HRA,HillDevAllowance,OverTime,
MedicalAdvance,GPFAdvance,MedicalAllowance,
Exgratia,HouseLoan,MedicalReimb,TravelAdvance,TABill,Other1,Other2,
TotalPayment,ToBank,ltaMasterId
)
values
(
empidnew, paydate,p_Basic,p_Add,p_special,p_conveyenceAllownce,
p_deputation,p_Personal,p_DA,p_AdditionalDA,p_HRA,p_HillDevAllowance,p_OverTim e,
p_MedicalAdvance,p_GPF,p_MedicalAllowance,
p_Exgratia,p_HouseLoan,p_MedicalReimb,p_TravelAdvance,p_TABill,p_Other1,p_Othe r2,
p_TotalPayment,ToBank,0
);
end if;
END$$
DELIMITER ;$$

---

Stored Procedure Vs Views
I am a little confused on the benefits of views when compared to stored procedures. When should you use a view and when should you use a stored procedure? Do either technology have a performance edge over the other?

---

Calling Php From Stored Procedure!
I was wondering if we can call a php program in stroed procedure in mysql so that I can produce reports for the tables.If possible please write the procedure to call the php from stored procedure.

---

Stored Procedure Problem
DELIMITER //
CREATE FUNCTION `ADD_DATE`(BaseDate DATE, PeriodOffset VARCHAR(20)) RETURNS DATE
BEGIN
DECLARE iDuration INT;
DECLARE iPeriod VARCHAR(10);
SET iDuration = SUBSTRING(PeriodOffset,1,INSTR(PeriodOffset,' '));
SET iPeriod = SUBSTRING(PeriodOffset,INSTR(PeriodOffset,' ')+1);
if iPeriod='DAYS' THEN
RETURN BaseDate;
else if iPeriod='MONTHS' then
RETURN DATE_ADD(BaseDate,INTERVAL iDuration MONTH);
else if iPeriod='YEARS' then
RETURN DATE_ADD(BaseDate,INTERVAL iDuration YEAR);
end if ;

END //
DELIMITER ;

---

Mysql Stored Procedure Help
I am trying to create a stored procedure with the following query. However it fails to execute. Can someone tell me what the problem is and how to rectify it.

DELIMITER $$

DROP PROCEDURE IF EXISTS `eftdb`.`Get_BondNav` $$
CREATE PROCEDURE `eftdb`.`Get_BondNav` (querydate DATE)
BEGIN
select sum(TRUNCATE(( d.etf_nav_change ) * (d.etf_tna / (SELECT sum( d.etf_tna )
FROM tbl_etfdaily d, tbl_etfmaster m
WHERE d.etf_ticker = m.etf_ticker
AND m.etf_type = 'Bonds'
AND d.etf_date = querydate)),4) * 100) as wnav
FROM tbl_etfdaily d, tbl_etfmaster m
WHERE d.etf_ticker = m.etf_ticker
AND m.etf_type = 'Bonds'
AND d.etf_date = querydate group by d.etf_date
END $$

DELIMITER ;

Error message

Script line: 4You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select sum(TRUNCATE(( d.etf_nav_change ) * (d.etf_tna / (SELECT sum( d.etf_tna )' at line 3

---

Stored Procedure Needed
I have a table named players with these fields:

playerid {int}
teamid {int}
jersey {int}
playername {varchar(50)}
age {int}
And a table named pitchcounts with these fields:

id {int}
gamenum {int}
playerid {int}
pitchcount {int}
Now, what I need is a stored procedure to return the number of pitches each player pitched in a week where the weekno is passed
and the days rest required and next availability date of the pitcher based on the criteria below:

In-House Ages 13-16 (Babe Ruth)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-95 3 Rest

********************************************************
In-House Ages 11 & 12 (Majors-American, National & Continental)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-85 3 Rest
No more than 125 pitches in a week. 4 12's can pitch but no more than 240 pitches no matter how many games in a week.

********************************************************
Travel Ages 8, 9, 10, 11 & 12
--------------------------------------------------------
Player can pitch up to three innings in a weekend. Two innings doesn't affect In-House the next day, but prior In-House pitching does apply. If 61 or more pitches on Thurs or Sat no travel that weekend. A travel manager must obtain approval from the In-House manager in order to pitch a player for more than one inning if the in-house team has a game following the travel team on the same day. If pitchers does pitch more than 1 inning in travel, pitcher limited to 60 pitches In-House.
If travel follows In-House on same day, player can pitch in travel if no more than 60 pitches thrown in-house. These rules will be in place until may 1st. We want these rules to be followed in spirit as well as literally. If abuses are found, even if within rules, we will change them after may 1st. Under no circumstances is a player able to be made ineligible for In-House play.

********************************************************
In-House Ages 10 and 9'S in Majors (Majors American, National, Continental)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-75 3 Rest
********************************************************
Double A In-House (AA)
--------------------------------------------------------
No more than 40 pitches in a game or 80 for a week.

********************************************************
Additional In-House Notes
--------------------------------------------------------
If a pitcher gets to 85 (75 at age 10) pitches in a game in the middle of a batter, they can finish the batter only. Pitching results must be reported by email to Director within 24 hours of the game by the winning manager. Pitcher can not re-enter to pitch in the same In-House game once removed from the mound. Pitcher receives seven warm-up pitches before their first inning on mound and five every inning thereafter. Warmup pitches no not count toward total counts. Pitches to final batter, if count gets past game or week totals, don't count toward totals. 12 year olds in Continental League can not pitch.

Rest Examples:

if 20 or less on Monday, available on Tuesday
if 21-40 on Monday, available on Wednesday
if 41-60 on Monday, available on Thursday
if 61 or more on Monday, available on Friday

---

Can't Create A Stored Procedure
I am trying to create a stored procedure and cannot! I can't even create on using the examples right out of the manual.... Please help....

I am using version 4.1.7 Standard on Solaris 8 (creating tables, indexes etc works)

this is an example that i have tried...

---

Calling A Stored Procedure In VB.Net
I am a novice user of the SQL serversbut i have managed to create a Proc, however how do i go about calling it VB.net.

---

Stored Procedure In MySQL5
I am writing this stored procedure and it keeps erroring out:
CODEcreate procedure RetensionRate ()
BEGIN
CREATE TEMPORARY TABLE mytemptable (Week int,  beginning_Count bigint, Ending_Count bigint, Retension_Rate decimal);

insert into mytemptable select Week,  Beginning_Count,Ending_Count, Retension_Rate * 100 from RetensionSummary order by week;

select * from mytemptable;
END

---

How To Get Stored Procedure Parameters
I wonder if anyone knows how to collect stored procedure parameters?

To get procedures list I can use query:
SELECT * FROM information_schema.ROUTINES

To get procedure body:
SHOW CREATE PROCEDURE proc_name
(I can parse and find params - not nice solution)

I am searching exacly for result like this query returns:
SELECT param_list FROM mysql.proc --> result: "IN inEngineID int"

I can't use this becouse table mysql.proc is system table and is blocked by db providers.

There is for sure solution becouse mysql query browser shows params for each procedure in tree. I downloaded sources for this program but didn't find listing procedure.

---

How Can I Display A Stored Procedure
Once a stored procedure has been created, how can I display its contents.  I know how to run it and the show procedure status shows which procedures are defined, but the actual code for the procedure is not shown.  

---

Parsing XML From A Stored Procedure
I would like to know if I can pass XML data to a MYSQL stored procedure
and then process the XML file to insert its values into a table.
Something like the "SELECT ... FROM OPENXML " from TSQL

---

Creating Stored Procedure
I'm using Mysql server 4.1.21. I try to create a strored procedure, but it fails:

create procedure test() select * from states;
I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'procedure test() select * from states' at line 1

If I try to create the same procedure via MySqlAdministrator I get the same error. Why?

---

Stored Procedure And Functions?
I would like to ask some questions about stored procedure and functions.
I know some of their difference when it comes to returning values, stored procedures can return many values while stored functions can only return single value.

I want to know if they have difference when it comes to speed, is stored procedures faster than stored functions?

Follow up question,doing some procedures and functions in my database, does it make my applications fast?

Actually I studied this features hoping to make my applications run fast. So that's why Im asking about the speed.

---

MySQL Stored Procedure
Below is an Oracle SP (works fine).

CREATE OR REPLACE PROCEDURE addPerson
(Id IN varchar2, name IN varchar2, age IN varchar2, address IN varchar2)
IS
BEGIN
INSERT INTO Person_tab
(Id, name, age, address)
VALUES
(Id, name, age, address);
END;

I use my VB app to add data to Person_tab. Basically I want to do exactly the same only for MySQL.

---

Stored Procedure Language
I have 2 main questions:

1. Was just wondering what the language used by MySQL is called?! (kinda..SQL Server uses T-SQL and Oracle uses PL/SQL). Is there a similarty with any other language?

2. Is there somewhere a good reference on how to create and use Stored Procedures and Functions?

---

Stored Procedure Automation?
Can I run a stored procedure automatically after a table is appended in my DB? How would I do this?

---

How To Exit From Stored Procedure?
in my stored procedure i have some if else statements:

SET @status = "";

IF (code is invalid...
SET @status = "Invalid code.";
SELECT @status;
END IF;

IF (name is invalid..
SET @status = "Invalid name.";
SELECT @status
END IF;

IF (code and name already exists)
SET @status = "Record exists.";
SELECT @status
ELSE
SET @status = "Record added.";
SELECT @status
END IF;

SELECT @status;

The @status always return the value from the last IF .. ELSE statement. Since RETURN can't be used in a procedure, what syntax should i use to immediately exit the stored procedure if the first IF statement is true?

---

Stored Procedure Tool
I'm using mySQL 5 and i'm trying to create some stored procedures.

Does anyone know what do I have to do, to make "MySQL Query Browser" works like if i was using the mySQL console ?

Cause, when i try to run this code at the "MySQL Query Browser" or another mySQL query tool:

DELIMITER |
DROP PROCEDURE IF EXISTS sp_declareG|
CREATE PROCEDURE sp_declare (P INT)
BEGIN
DECLARE x INT;
DECLARE y INT DEFAULT 10;
SET x = P*y;
SELECT x;
END|
DELIMITER ;

I get several errors. BUT, if i paste this inside the mySQL console it works FINE ! What do i have to do to make "MySQL Query Browser" works just like the console ?

---

Advanced Stored Procedure
I am currently working on a table which contains Data (a Locator) which I use to calculate a distance to a certain Person (another Locator).

I used to calculate this Distance in Java since the calcutlation is not quite easy and I only calculate it for 20-30 rows at once. But now I want to sort the table and display a portion of 20-30 rows of the sorted table.

I thought selecting 1000+ rows in java, then calculating the distance for each row, sorting it (in java) and finally only displaying 20-30 rows and discarding the Rest was stupid and inefficient.

Therefore I want to calculate the distance in a MySql stored Procedure and sort the resulting table for the selection of 20 rows.

The way I thought of doing it was using a temporary table to store the distances and then join with my normal table, sorting, and finnaly selecting.

So would it be possible to do this in a stored procedure if I need the following to calculate the distance:

1) converting chars in a String to position in the alphabeth (maybe using SUBSTR()UPPER()ASCII()-64)
2) converting Numbers stored as chars into ints (maybe CAST())
3) are acos() cos() and sin() functions available?
4) is Pi availiable as a constant?
5) I suppose calculation ( + - * / ) with foating point variables is no problem

So the mathematical problem seems not to be too hard. But my main question is if the described way of using a temporary table is possible and is it possible for stored procedures to call other stored procedures?

---

Simple Stored Procedure
I want to built a receipt function for a cloumn named Receipt:

I want to append Column named Id to the Date:

So for example:

id = 1001;
"date" = 011807;

Receipt = 1001011807;

---

Crosstab Stored Procedure
I have a stored procedure, which works, as follows:

BEGIN
Select timesheet_eventid, timesheet_teetime, timesheet_tee,
sum(IF(timesheet_player = "P1", timesheet_membid, '')) AS "Player1",
sum(IF(timesheet_player = "P2", timesheet_membid, '')) AS "Player2",
sum(IF(timesheet_player = "P3", timesheet_membid, '')) AS "Player3",
sum(IF(timesheet_player = "P4", timesheet_membid, '')) AS "Player4"
FROM view_crosstab
WHERE timesheet_eventid=prmEventID
Group by timesheet_teetime;
END

However Instead of putting timesheet_membid in the body of the result, I want to put "player". I have tried the following, however it only puts in P1 (not P2, P3, or P4).

BEGIN
Select timesheet_eventid, timesheet_teetime, timesheet_tee,
(IF(timesheet_player = "P1", player, '')) AS "Player1",
(IF(timesheet_player = "P2", player, '')) AS "Player2",
(IF(timesheet_player = "P3", player, '')) AS "Player3",
(IF(timesheet_player = "P4", player, '')) AS "Player4"
FROM view_crosstab
WHERE timesheet_eventid=prmEventID
Group by timesheet_teetime;
END

How do I get this cross to work properly?

---