|
|
I am trying to create a stored procedure whereby I can pass it 3 paramaters - the table name, the field name and some data. The procedure would then check whether that data existed in the column of the table. The procedure I wrote looks something like
| Text | 1
23
45
67
89
1011
1213
1415
1617
1819
| DELIMITER //
DROP PROCEDURE IF EXISTS `check_field_in_table`//
CREATE PROCEDURE check_field_in_table (
arg_field_data VARCHAR(255),arg_field_name VARCHAR(30),
arg_table_name VARCHAR(30),OUT out_records_found INT)
READS SQL DATABEGIN
SELECT COUNT(arg_field_name)
FROM arg_table_nameWHERE arg_field_name = arg_field_data
INTO out_records_found;END//
DELIMITER ; |
But it freaks out saying it cannot find dops.arg_table_name (dops is the name of the db). Is it possible to make it look inside the arg_table_name and arg_field_name variables rather than just interpretting as the name?
Thanks in advance
|
|
|
|
Probably sounds really obvious, but have you checked that the database and table are in MySql, and that there is a connection to it?
|
|
|
|
yea that all works - have run many other queries to the db so far with no incident but this is the first where i am trying to pass the table name as a variable
|
|
Register (or login) on our website and you will not see this ad.
|
|
|
You will need to use some kludges.
Mick's Blog | Greasemonkey scripts
'Sir, please... will you not share your wisdom with us?'
'I have no wisdom,' he told her.
'Your experiences, then?'
'They have been trivial, uninteresting, and full of error.'
Iain M. Banks - Feersum Endjinn
Edited by micksharpe (Thu 12-Feb-09 12:56:09)
|
|
|
|
sledgehammer/nut?? is there any given reason why you can't just check the size of the results set following your query?
|
|
|
That looks just like what I need. I have tried again and come up with:
| Text | 1
23
45
67
89
1011
1213
1415
1617
1819
2021
2223
| DELIMITER //
DROP PROCEDURE IF EXISTS `check_field_in_table`//
CREATE PROCEDURE check_field_in_table (
arg_field_data VARCHAR(255),arg_field_name VARCHAR(30),
arg_table_name VARCHAR(30),OUT out_records_found INT)
READS SQL DATABEGIN
SET @query = CONCAT('SELECT COUNT(', arg_field_name, ')
FROM ' , arg_table_name , 'WHERE ' , arg_field_name , ' = ' , arg_field_data, '
INTO ' , out_records_found , '');
PREPARE select_query FROM @query;EXECUTE select_query;
DEALLOCATE PREPARE select_query; END//
DELIMITER ; |
However it still isn't quite right. I get the error:
ERROR 1064 (42000): 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 'NULL' at line 1
|
|
|
You can't plug an output field into the CONCAT function since the field has no value. You will need to work around this.
Mick's Blog | Greasemonkey scripts
'Sir, please... will you not share your wisdom with us?'
'I have no wisdom,' he told her.
'Your experiences, then?'
'They have been trivial, uninteresting, and full of error.'
Iain M. Banks - Feersum Endjinn
|
|
|
The problem i am having is that I cannot get the query to run in the first place 
|
|
|
I have finally got it working using the following (if anyone cares!) :
| Text | 1
23
45
67
89
1011
1213
1415
1617
1819
2021
2223
| DELIMITER //
DROP PROCEDURE IF EXISTS `check_data_in_db`//
CREATE PROCEDURE check_data_in_db (
arg_field_data VARCHAR(255),arg_field_name VARCHAR(30),
arg_table_name VARCHAR(30),OUT out_records_found INT)
READS SQL DATABEGIN
SET @query = CONCAT('SELECT COUNT(', arg_field_name, ') AS out_records_found
FROM ' , arg_table_name , 'WHERE ' , arg_field_name , ' = ' , arg_field_data, '
');PREPARE select_query FROM @query;
EXECUTE select_query;DEALLOCATE PREPARE select_query;
END//
DELIMITER ;code tag doesnt show bottom line! |
Thank you very much for your help - it might have taken me many more hours than it should have but progress has been made!
Edited by deleted (Thu 12-Feb-09 17:55:57)
|
|
|
Mick's Blog | Greasemonkey scripts
'Sir, please... will you not share your wisdom with us?'
'I have no wisdom,' he told her.
'Your experiences, then?'
'They have been trivial, uninteresting, and full of error.'
Iain M. Banks - Feersum Endjinn
|
Print Thread
deleted
