Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1427
  • Last Modified:

Dynamic SQL in Stored procedures

Hi all,

Is there any way of creating dynamic sql queries in a stored procedure in MySQL?

In SQL Server there are a few ways of doing this using sp_executesql

I have included an example for SQL server using the pubs database.
The general idea here is that the parameters @emp_id, @fname, @job_lvl would be passed as parameters to a stored procedure.
The procedure will then check each in turn and see if it's null, if not it will append the appropriate bit of the where clause to the main query.

--EXAMPLE--

DECLARE @SQLString NVARCHAR(500)
DECLARE @emp_id varchar(255)
DECLARE @fname varchar(255)
DECLARE @job_lvl int
DECLARE @where bit

SET @emp_id = null
SET @fname = 'Helen'
SET @job_lvl = null
set @where = 0

SET @SQLString = N'SELECT * FROM pubs.dbo.employee'

IF @emp_id is not null
begin
      IF @where = 0
      begin
            SET @SQLString = @SQLString +  N' WHERE emp_id =' + '''' + @emp_id+ ''''
            SET @where = 1
      end
      else
      begin
            SET @SQLString = @SQLString +  N' AND emp_id =' + '''' + @emp_id + ''''            
      end
end

IF @fname is not null
begin
      IF @where = 0
      begin
            SET @SQLString = @SQLString +  N' WHERE fname =' + '''' + @fname + ''''
            SET @where = 1
      end
      else
      begin
            SET @SQLString = @SQLString +  N' AND fname =' + '''' + @fname + ''''            
      end
end

IF @job_lvl is not null
begin
      IF @where = 0
      begin
            SET @SQLString = @SQLString +  N' WHERE job_lvl =' + cast(@job_lvl as varchar)
            SET @where = 1
      end
      else
      begin
            SET @SQLString = @SQLString +  N' AND job_lvl =' + cast(@job_lvl as varchar)            
      end
end
PRINT @SQLString
EXECUTE sp_executesql @SQLString
0
VincentLawlor
Asked:
VincentLawlor
1 Solution
 
akshah123Commented:
You can use PREPARE to first prepare the string as an sql command and then execute it....
Something like ...

SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

check out

http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html
0
 
VincentLawlorAuthor Commented:
Thanks that works a charm.

Just in case anybody is interested the MySQL equivalent of the example is as follows:

--EXAMPLE--

SET @emp_id = null;
SET @fname = 'Helen';
SET @job_lvl = null;
SET @where = 0;

SET @SQLString = 'SELECT * FROM pubs.dbo.employee';

IF @emp_id IS NOT NULL THEN
     IF @where = 0 THEN
          SET @SQLString = CONCAT(@SQLString, ' WHERE emp_id =','''', @emp_id,'''');
          SET @where = 1;
     ELSE
          SET @SQLString = CONCAT(@SQLString, ' AND emp_id =','''', @emp_id,'''');
     END IF;
END IF;

IF @fname IS NOT NULL THEN
     IF @where = 0 THEN
          SET @SQLString = CONCAT(@SQLString, ' WHERE fname =', '''', @fname, '''');
          SET @where = 1;
     ELSE
          SET @SQLString = CONCAT(@SQLString, ' AND fname =', '''', @fname, '''');
     END IF;
END IF;

IF @job_lvl IS NOT NULL THEN
     IF @where = 0 THEN
          SET @SQLString = CONCAT(@SQLString, ' WHERE job_lvl =', @job_lvl);
          SET @where = 1;
     ELSE
          SET @SQLString = CONCAT(@SQLString,' AND job_lvl =', @job_lvl);        
     ENDIF;
END IF;

PREPARE stmt FROM @SQLString;
EXECUTE stmt;
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now