What is the advantage of using @ParmDefinition in sp_executesql
DECLARE @id int
DECLARE @name nvarchar(20)
SET @id = 5
SET @name = 'Paul'
What is the difference between these two parameters:
Set @SQLQueryInnen = 'SELECT * FROM someTable WHERE ID = ' + @id + ' AND NAME = ''' + @name + ''''
Execute sp_Executesql @SQLQueryInnen
and
Set @SQLQueryInnen = 'SELECT * FROM someTable WHERE ID = @id AND NAME = @name'
Set @ParmDefinition = '@id int, @name nvarchar(20)'
Execute sp_Executesql @SQLQueryInnen, @ParmDefinition, @id
So far I've only seen redundancies for declaring the datatype @id and @name twice when using @ParmDefinition. On the other hand, "string-building" seems a little easier with @ParamDefinition.
source to share
You avoid string typing code where you have to convert everything to a string so you can insert it into a parameter @SQLQueryInnen
and then enter problems because you have to decide how to safely and unambiguously perform conversions to and from strings from the correct original data types.
For int
s, the conversion problems aren't very obvious. But if you look at the amount of problems people have reported (here and in other forums) where they have problems converting between datetime
and strings, you will realize that this is indeed causing problems. It is best to store data in its natural form.
source to share
I see nobody mentioned one of the most important things. When you use a parameterized query, your execution plans are cached.
Your request:
SELECT *
FROM someTable
WHERE ID = @id
AND NAME = @name;
Its execution plan will be stored in memory and it will be reused every time you request it (which is a big advantage). Meanwhile, if you generate your code using string concatenation like this:
Set @SQLQueryInnen = 'SELECT * FROM someTable WHERE ID = ' + @id + ' AND NAME = ''' + @name + ''''
Execute sp_Executesql @SQLQueryInnen
Your code will generate an execution plan for each combination of parameters (unless it repeats itself), and the cached plan will not be reused. Imagine that you are going through @Id = 1
and @Name = 'Paul'
your generated query will look like this:
SELECT *
FROM someTable
WHERE ID = 5
AND NAME = 'Paul';
If you change your name to 'Rob'
, your generated query will look like and SQL Server will have to create a new plan for it:
SELECT *
FROM someTable
WHERE ID = 5
AND NAME = 'Rob';
Sense plans will not be reused. Hope it helps.
This article explains this in a little more detail: EXEC vs. sp_executeSQL (Don't rely on the article title, it explains the exact differences you asked for your question). Quoting from this:
TSQL string is created only once, after that every time the query is called with sp_executesql, SQL Server gets the schedule query from the cache and reuse it
source to share