CODEWITHSUNDEEP
mysqldynamic-sql
Eric Anderson
Eric Anderson

Reputation: 366

How to put single quotes around variables in a dynamic query

I am trying to build a dynamic query. Initially @Query is set to the query string and then I want to dynamically add the WHERE clause. It works, except it isn't putting the single quotes around the strings in this case @val. This causes an error. How do I include the single quotes so that it adds them correctly?

This is what I've tried:

SET @Query = @Query + ' WHERE ' + '' + @param + ' ' + @operator + ' ' + '' + @val + '' ;

Thanks!

Upvotes: 2

Views: 33516

Answers (5)

Bill Karwin
Bill Karwin

Reputation: 562731

If you're using MySQL, you will need to know that + isn't the string-concatenation operator (that's non-standard syntax in Microsoft SQL Server and Access).

MySQL uses a builtin function CONCAT(): 

SET @Query = CONCAT(@Query, ' WHERE ', @param, @operator, '?');
PREPARE stmt FROM @Query;
EXECUTE stmt USING @val;
DEALLOCATE stmt;

Or you can set ANSI mode (or more specifically PIPES_AS_CONCAT mode) and use SQL-standard string concatenation:

SET sql_mode = 'ANSI';
SET @Query = @Query || ' WHERE ' || @param || @operator || '?';
PREPARE stmt FROM @Query;
EXECUTE stmt USING @val;
DEALLOCATE stmt;

For values, you don't need the ? placeholder in quotes in the SQL expression, in fact it must not be in quotes, or else it'll be a literal '?' string, not a parameter placeholder.

You can't parameterize column names or operators, only values.

Upvotes: 0

ridwannhidayat
ridwannhidayat

Reputation: 115

BEGIN

set @mulai=_tanggalmulai;
set @akhir=_tanggalakhir;

IF @mulai <> '' && @akhir <> '' THEN
SET @setWhere=' and date(created_at) between STR_TO_DATE(@mulai,''%d/%m/%Y'') and STR_TO_DATE(@akhir,''%d/%m/%Y'')';
ELSEIF @mulai <> '' && @akhir = '' THEN
SET @setWhere=' and date(created_at) > NOW()';
ELSEIF @mulai = '' && @akhir <> '' THEN
SET @setWhere=' and date(created_at) < NOW()';
ELSE
SET @setWhere=' ';
END IF;

SET @qry=CONCAT('SELECT id,norekammedik,nama,jeniskelamin,tempatlahir,nohp,alamat,DATE_FORMAT(tanggallahir,''%d/%m/%Y'') as tanggallahir,DATE_FORMAT(created_at,''%d/%m/%Y'') as created_at FROM pasien where 1=1',@setWhere,' ORDER BY id DESC');

PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

Upvotes: 0

Bob
Bob

Reputation: 1

Try this code:

DECLARE @SQ [varchar](1) = ''''

SET @Query = @Query + ' WHERE ' + @SQ + @param + @SQ + ' ' + @operator + ' ' + @SQ + @val + @SQ;

Upvotes: 0

Seroczynski
Seroczynski

Reputation: 492

You would have to place a quote in between the quotes, but escape it so it doesn't break your code. It would look like the following:

SET @Query = @Query + ' WHERE ' + '' + @param + ' ' + @operator + ' ' + '\'' + @val + '\'' ;

Edit: Eric Anderson's answer works as well. Take from the MySQL 5.0 Manual

A “'” inside a string quoted with “'” may be written as “''”.

Upvotes: 1

Eric Anderson
Eric Anderson

Reputation: 366

This worked:

SET @Query = @Query + ' WHERE ' + '' + @param + ' ' + @operator + ' ' + '''' + @val + '''' ;

Programming can be very stupid sometimes. Good ol' 4 single quotes.

Thanks anyways guys.

Upvotes: 0

Related Questions