SEO

#1 (**permalink**) 02-28-2008, 07:13 AM

I have such StoredProc (Function)
Is it possible to pass 'Table' to stored proc as argument?

code
-----------------------------
CREATE FUNCTION `DDT`.`GetSelectMd5`() RETURNS varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;

DECLARE row_crc32 INT;
DECLARE column_concat longtext;
DECLARE column_md5 varchar (64);
DECLARE reader CURSOR FOR SELECT CRC32_DATA FROM Table;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN reader;
SET column_concat = '';
WHILE not done DO
FETCH reader INTO row_crc32;
SET column_concat = concat(column_concat,row_crc32);
end while;
CLOSE reader;
set column_md5 = md5(column_concat);
RETURN md5(column_md5);
END
-----------------

How to use TableName from variable inside CURSOR FOR SELECT ...FROM
'TableName'?

#2 (**permalink**) 02-28-2008, 07:13 AM

Artur Baæ wrote:
> I have such StoredProc (Function)
> Is it possible to pass 'Table' to stored proc as argument?
>
> code
> -----------------------------
> CREATE FUNCTION `DDT`.`GetSelectMd5`() RETURNS varchar(255)
> BEGIN
> DECLARE done INT DEFAULT 0;
>
> DECLARE row_crc32 INT;
> DECLARE column_concat longtext;
> DECLARE column_md5 varchar (64);
> DECLARE reader CURSOR FOR SELECT CRC32_DATA FROM Table;
> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
> OPEN reader;
> SET column_concat = '';
> WHILE not done DO
> FETCH reader INTO row_crc32;
> SET column_concat = concat(column_concat,row_crc32);
> end while;
> CLOSE reader;
> set column_md5 = md5(column_concat);
> RETURN md5(column_md5);
> END
> -----------------
>
> How to use TableName from variable inside CURSOR FOR SELECT ...FROM
> 'TableName'?
>
>
>

Unfortunately, with the current implementation of cursors, you can't.
You need to know the table name in advance if you want to use cursors.
A variable table name means that you should use dynamic SQL (server side
prepared statements), but they are not currently supported with cursors.

ciao
gmax

--
_ _ _ _
(_|| | |(_|><
_|
http://gmax.oltrelinux.com

#3 (**permalink**) 02-28-2008, 07:13 AM

thanks for answer.
I Solved this in a little different way
with Temporary Table Engine=Memory

So the table name is static 'temp'
before execution of function i dynamicaly create table in memory then
execute function on temp and at the end i drop the table
so i dont have to pass a table to function.
But programing this way is a global variable programing like in visual basic
....
Could lead to errors in recursive functions ....
It is pretty hard for person how use OO programing only switch back to
procedural programing in SQL Stored Proc....

drop table if exists temp;
CREATE temporary TABLE temp ENGINE=Memory
Select md5(concat(
CASE WHEN Indeks NOT LIKE '' THEN Indeks ELSE '' END,
..................................
)) AS CRC32_DATA From CountryE order by CRC32_DATA asc;
Select GetSelectMd5() as MD5;
drop table if exists temp;

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

StoredProc: How to pass 'table' as argument ?