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
LVL 4
VincentLawlorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.