MySQL Foundation----Dynamic SQL statements

respect for the original: http://blog.csdn.net/abc19900828/article/details/39501643Dynamic SQL statement Basic syntax
1: Normal SQL statements can be executed with exec

Eg:select * from TableName
Exec (' select * from TableName ')
Exec sp_executesql n ' select * from TableName '--please note that the string must be added n before

2: Field name, table name, database name, etc. as variables, you must use dynamic SQL

eg
DECLARE @fname varchar (20)
Set @fname = ' Filedname '
Select @fname from TableName-error, does not prompt for errors, but the result is fixed value filedname, not required.
Exec (' select ' + @fname + ' from TableName ')--pay attention to the space on the edge of the single quotation mark before and after the plus sign

Of course, changing the string to a variable can also
DECLARE @fname varchar (20)
Set @fname = ' Filedname '--set field name

DECLARE @s varchar (1000)
Set @s = ' SELECT ' + @fname + ' from TableName '
Exec (@s)--Success
EXEC sp_executesql @s--This sentence will be an error



DECLARE @s Nvarchar (1000)--note here to Nvarchar (1000)
Set @s = ' SELECT ' + @fname + ' from TableName '
Exec (@s)--Success
EXEC sp_executesql @s--this sentence is correct

3. Output parameters
DECLARE @num int,
@sqls nvarchar (4000)
Set @sqls = ' SELECT count (*) from TableName '
EXEC (@sqls)
--How do I put the exec execution result into a variable?

DECLARE @num int,
@sqls nvarchar (4000)
Set @sqls = ' Select @a=count (*) from TableName '
EXEC sp_executesql @sqls, N ' @a int output ', @num output
Select @num

In the stored procedure, dynamically stitching a string and then executing it.

SQL code

1. DROP PROCEDURE IF EXISTS Demosp;
2. CREATE PROCEDURE Demosp ()
3. BEGIN
4. set @sqlstr =concat ("SELECT count (*) from", "dual");
5. Prepare stmt from @sqlstr;
6. EXECUTE stmt;
7. deallocate prepare stmt;
8. END;

MySQL Foundation----Dynamic SQL statements