February 23, 2014 at 10:57 pm
Comments posted to this topic are about the item A Quick Tour of sp_ExecuteSQL
February 24, 2014 at 1:34 am
Nice article. I'm still working on writing my first technical article. It's a bit of a daunting task, but I'm getting there.
Regarding sp_ExecuteSQL: Dynamic SQL is one of those things I wish developers could un-learn as they often using it as an easy fix for bad design.
I do have a quick question - what happens with the execution plan?
- would the execution plan be regenerated on each run of the main procedure?
- does/can it ever reuse execution plans on the dynamic SQL sections?
Anything can be fixed
February 24, 2014 at 1:54 am
Thank you Jonathan for this article.
Just a quick note on the last query, it will fail if a table is not in the default user schema.
This will work for all schemas;
DECLARE @TSQLDefinition NVARCHAR(500)
DECLARE @TableNameVar NVARCHAR(100)
DECLARE @SCHEMA_IDINT
DECLARE @TableList TABLE
(
TableName NVARCHAR(100)
,schema_id INT
)
/* collect table names into a table variable */
INSERT @TableList
( TableName,schema_id)
( SELECT Name ,schema_id
FROM sys.tables
WHERE type_desc = N'USER_TABLE')
WHILE EXISTS ( SELECT *
FROM @TableList )
BEGIN
/* build the TSQL statement we want executed for one table */
SELECT TOP 1
@TableNameVar = tl.TableName
,@SCHEMA_ID = tl.schema_id
FROM @TableList AS tl
SET @TSQLDefinition = N'SELECT COUNT(*) AS '
+ QUOTENAME(@TableNameVar) + N' FROM '
+ QUOTENAME(SCHEMA_NAME(@SCHEMA_ID)) + NCHAR(46)
+ QUOTENAME(@TableNameVar);
/* execute the TSQL */
EXEC sys.sp_executesql @TSQLDefinition;
/* remove the tablename from the list of tables*/
DELETE FROM @TableList
WHERE TableName = @TableNameVar
END
GO
February 24, 2014 at 4:03 am
Daniel Brink (2/24/2014)
Nice article. I'm still working on writing my first technical article. It's a bit of a daunting task, but I'm getting there.Regarding sp_ExecuteSQL: Dynamic SQL is one of those things I wish developers could un-learn as they often using it as an easy fix for bad design.
I do have a quick question - what happens with the execution plan?
- would the execution plan be regenerated on each run of the main procedure?
- does/can it ever reuse execution plans on the dynamic SQL sections?
There's a great article on that by Erland Sommarskog on exactly that, cache plan reuse and dynamic queries.
Parameterized queries have reusable plans, sometimes that's a bad thing when different parameter values would benefit from different plans.
February 24, 2014 at 5:36 am
If I'm not wrong, the way sp_executesql is being used in this article is not the right one when we think about a better plan cache use.
Something like the following sample would be most appropriate.
EXEC SP_EXECUTESQL N'SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders WHERE OrderID = @p', N'@p INT', 102480
February 24, 2014 at 7:24 am
Great Article, but I did notice something weird in my testing.
I started with the first example which was a simple select * from a table and that worked without a hitch. I then tried the second example by adding a where condition. Again I had no problem so I went on to the 3rd example of adding multiple where conditions. Now the problems started.
I keep getting:
Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near 'field 2 name'.
I tried multiple fields of different types in different orders and kept getting the error.
The only real difference between my tests and the examples was that I fully qualified my table. So just for the heck of it I removed the database and schema thinking there was no way it could be that but was willing to try anything and sure enough it started to work.
Thanks,
John
February 24, 2014 at 9:28 am
How is this different from plain old EXEC(@SomeSQLStatement)?
BR
February 24, 2014 at 9:38 am
BXRWXR (2/24/2014)
How is this different from plain old EXEC(@SomeSQLStatement)?BR
One big difference is that with sp_ExecuteSQL you can define parameters external to the @SomeSQLStatement variable, thereby reusing the dynamic sql with different parameter values.
February 24, 2014 at 10:21 am
Tom Bakerman (2/24/2014)
BXRWXR (2/24/2014)
How is this different from plain old EXEC(@SomeSQLStatement)?BR
One big difference is that with sp_ExecuteSQL you can define parameters external to the @SomeSQLStatement variable, thereby reusing the dynamic sql with different parameter values.
The big significance of passing parameters is that using EXEC with an external interface that builds @SomeSQLStatement creates a SQL injection vulnerability. Properly coded parameterized dynamic SQL with sp_executeSQL avoids this.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 24, 2014 at 1:06 pm
sp_executesql does promote execution plan reuse. You can also declare OUTPUT parameters.
February 24, 2014 at 1:46 pm
Cool. Thanks for the clarification.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply