Menu
@statement is the SQL we wish to execute. @parameterDefinition is a string containing a definition of all parameters referenced in @statement. Each parameter and type found @statement is listed. The name and type are separated by a space. What are the real world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server using EXEC (@SQL) versus EXEC SPEXECUTESQL @SQL?
Active1 month ago
I have to convert a MSSQL stored proc that passes a
varchar
that is a query:This isn't working. I'm pretty sure that
EXEC
and EXECUTE
aren't MySQL commands, but CALL
doesn't work either. Does anyone know if it's even possible to have something like JavaScript's
Qantas 94 Heavyeval
function for MySQL?12.6k1515 gold badges5757 silver badges7575 bronze badges
aaronaaarona15.8k3838 gold badges108108 silver badges157157 bronze badges
4 Answers
EXECUTE is a valid command in MySQL. MySQL reference manual
Brandon BodnarBrandon Bodnar7,80011 gold badge3131 silver badges4141 bronze badges
TengizTengiz
This is the SQL equivalent of
eval(my_string);
:Basically I combined the existing answers, neither tells you how to do eval exactly.
If you want to add parameters, you can use this:
And to answer the original question exactly:
Note that the
kenorbPREPARE .. FROM
statement wants a session variable (prefixed with @
). If you try to pass a normal variable, it will throw its hands up in the air and it just won't care.82.3k3535 gold badges459459 silver badges469469 bronze badges
LucLuc1,57622 gold badges2424 silver badges3030 bronze badges
The
EXECUTE
MySQL command can only be used for one prepared statement.If case you want to execute multiple queries from the string, consider saving them into file and source it, e.g.
kenorbkenorb
82.3k3535 gold badges459459 silver badges469469 bronze badges
Not the answer you're looking for? Browse other questions tagged mysqlevalevaluationexecexecute or ask your own question.
Active4 years, 5 months ago
What are the real world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server using
versus
It provides an inside scoop on the development of the company and has been described as “riveting.”When Shoe Dog came out in 2016, it marked the first time Knight talked about any of this. It also discusses Knight’s personal journey through the process and shares some moving and remarkable insights. Collins and Porras have encapsulated the findings of a six-year study conducted by the Stanford University Graduate School of Business into one page-turner of a book. Small business management book pdf. The man has always been something of an enigma. Shoe Dog is available in hardcover, paperback, a Kindle edition, as an audiobook and on CD.
?
Anup Kattel1,08911 gold badge1111 silver badges2121 bronze badges
Ash MachineAsh Machine4,64099 gold badges4141 silver badges4848 bronze badges
5 Answers
sp_executesql
is more likely to promote query plan reuse. When using sp_executesql
, parameters are explicitly identified in the calling signature. This excellent article descibes this process.The oft cited reference for many aspects of dynamic sql is Erland Sommarskog's must read: 'The Curse and Blessings of Dynamic SQL'.
Mitch WheatMitch Wheat264k3636 gold badges415415 silver badges507507 bronze badges
The big thing about SP_EXECUTESQL is that it allows you to create parameterized queries which is very good if you care about SQL injection.
DJ.DJ.15k33 gold badges3636 silver badges4444 bronze badges
Microsoft's Using sp_executesql article recommends using
sp_executesql
instead of execute
statement.Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server, sp_executesql is more efficient than EXECUTE.
So, the take away: Do not use
Ganexecute
statement. Use sp_executesql
.Sql Execute Dynamic Query String
Gan3,70722 gold badges2828 silver badges4343 bronze badges
I would always use sp_executesql these days, all it really is is a wrapper for EXEC which handles parameters & variables.
Execute Query String Sql Server
However do not forget about OPTION RECOMPILE when tuning queries on very large databases, especially where you have data spanned over more than one database and are using a CONSTRAINT to limit index scans.
Unless you use OPTION RECOMPILE, SQL server will attempt to create a 'one size fits all' execution plan for your query, and will run a full index scan each time it is run.
This is much less efficient than a seek, and means it is potentially scanning entire indexes which are constrained to ranges which you are not even querying :@
Ten98Ten98
- Declare the variable
- Set it by your command and add dynamic parts like use parameter values of sp(here @IsMonday and @IsTuesday are sp params)
- execute the command
Execute Sql Query Online
SaraSara