Exec(@SQL)

Question

Exec(@SQL)

san43

Hall of Fame

Points: 3196
More actions
September 11, 2007 at 6:14 pm

#70241

I created stored procedure with in which sql statements are created like this:
declare @sql varchar(1000)
set @sql = ----(which is created using the inputs from stored proc parameters)
Exec (@SQL)
My question is what is this kind of SQL Statement called as? and is this builded SQL statement not shown when we trace this stored proc? becuase I traced this stored proc but I couldn't get the exact SQL statement what it is executing to find out the duration this SQL statement took.
Also what is dynamic SQL?
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

It's called dynamic SQL... basically it just means that you built your SQL string on the fly and executed it. It has it's up sides (flexibility) and it's down-sides (usually poor performance, security issues, complexity can go crazy pretty quickly).

You should be able to find it if you're running a profiler trace.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

Won't have poor performance if it's for multi-row batches... on busy systems, most batch procs will probably need to recompile due to data changes in as little as 5 minutes. GUI code is another story... If you use correctly parameterized sp_ExecuteSQL, you won't have recompiles even for single row GUI code but you will be limited to 4k characters (shouldn't be much of a problem for GUI code).

Probably won't have security issues if it's for batches with no GUI interface and the parameters are fed correctly. Lot's of Google hits on how to do it correctly.

And, you can always PRINT @sql if you want to see what it's gonna look like...

The other thing is, unless you are trying to make dynamic column names or dynamic FROM clauses, you probably don't need dynamic SQL at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

san43 Hall of Fame Points: 3196 More actions · Answer 3

How to get the execution plan for this kind of stored procs with dynamic SQl in it?

If I want to get the exact execution plan from production environment, do I need to run the stored proc with SHOW EXECUTION PLAN, if so , I am getting error like this object already exists?

Any reply is greatly appreciated.

noeld SSC Guru Points: 96590 More actions · Answer 4

for execution plan you will have to "print" it first to see how it looks like, and then execute it

* Noel

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

"Error like this object already exists?" What on Earth are you doing with the Dynamic SQL?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

san43 Hall of Fame Points: 3196 More actions · Answer 6

Can't I see the execution plan for the stored proc directly?

san43 Hall of Fame Points: 3196 More actions · Answer 7

Showing execution plan from general SQL Statement is easy. But how about getting execution plan for a SQL statement that is created from parameter inputs to the stored procedure as I donot know what inputs to give for that parameters.

Can I use the SQL statement that is produced by trace. How to get execution plan for that already build SQL statement i.e., how to get the execution plan with out actually executing the statement?

Thanks in advance.

Carlos Urbina-486044 SSC Enthusiast Points: 127 More actions · Answer 8

I would PRINT the created SQL, copy it from the bottom window in Query Analyzer (QA), paste it in a new QA window then click on the execution plan icon.