Dynamic if exists?

Hi,

I'm trying to create a stored procedure to check if a certain row
exists, and if so to update it - else insert it. Both table and id-
column are dynamic.
I'm familiar in MS SQL and would use dynamic sql to perform this task
there, however, I can't seem to get MySQL working.

Here's what I've got now:

*******************************

DELIMITER $$

DROP PROCEDURE IF EXISTS `test1`.`spParamTest` $$
CREATE PROCEDURE `test1`.`spParamTest` (tablename varchar(20),
idCol varchar(20),
updateCol varchaR(20),
idValue varchaR(20),
updateValue varchar(20))
BEGIN

set @sql_text:=concat('if exists (select * from ', tablename, '
where ''', idCol, ''' = ''', idValue , ''') then '
, 'select ''need to update'';'
, ' else select ''need to insert''; end if;');

prepare stmt from @sql_text;
execute stmt;
deallocate prepare stmt;


END $$

DELIMITER ;

*******************************

The @sql_text gotten from the concat seems to be valid but when I try
to execute it from the prepared statement it fails. Anyone able to
point me into the correct direction?

Any advice would be appreciated.

Regards,
Sjur

On 1 Mar, 15:05, s...@randh.org wrote:
> Hi,
>
> I'm trying to create a stored procedure to check if a certain row
> exists, and if so to update it - else insert it. Both table and id-
> column are dynamic.
> I'm familiar in MS SQL and would use dynamic sql to perform this task
> there, however, I can't seem to get MySQL working.
>
> Here's what I've got now:
>
> *******************************
>
> DELIMITER $$
>
> DROP PROCEDURE IF EXISTS `test1`.`spParamTest` $$
> CREATE PROCEDURE `test1`.`spParamTest` (tablename varchar(20),
> idCol varchar(20),
> updateCol varchaR(20),
> idValue varchaR(20),
> updateValue varchar(20))
> BEGIN
>
> set @sql_text:=concat('if exists (select * from ', tablename, '
> where ''', idCol, ''' = ''', idValue , ''') then '
> , 'select ''need to update'';'
> , ' else select ''need to insert''; end if;');
>
> prepare stmt from @sql_text;
> execute stmt;
> deallocate prepare stmt;
>
> END $$
>
> DELIMITER ;
>
> *******************************
>
> The @sql_text gotten from the concat seems to be valid but when I try
> to execute it from the prepared statement it fails. Anyone able to
> point me into the correct direction?
>
> Any advice would be appreciated.
>
> Regards,
> Sjur

You're making this far too complicated.
Look at the syntax for
INSERT ... ON DUPLICATE KEY UPDATE

On 1 Mar, 16:31, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> You're making this far too complicated.
> Look at the syntax for
> INSERT ... ON DUPLICATE KEY UPDATE

Thanks, that worked out nicely. The original idea was to make the sql
work in both mssql and mysql and guess I got kinda stuck with that
idea even though abandoning it to make sp's.
Or is it possible to do any kind of "if exists (select...)"-syntax
outside of sp's in mysql 5.x?

Regards,
Sjur