Link to home
Start Free TrialLog in
Avatar of VincentLawlor
VincentLawlor

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of akshah123
akshah123
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of VincentLawlor
VincentLawlor

ASKER

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;