在sp_executesql中使用@ParmDefinition有什么好处 [英] 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 options:

Set @SQLQueryInnen = 'SELECT * FROM someTable WHERE ID = ' + @id + ' AND NAME = ''' + @name + ''''
Execute sp_Executesql @SQLQueryInnen

和

Set @SQLQueryInnen = 'SELECT * FROM someTable WHERE ID = @id AND NAME = @name'
Set @ParmDefinition = '@id int, @name nvarchar(20)'
Execute sp_Executesql @SQLQueryInnen, @ParmDefinition, @id

到目前为止，在使用@ParmDefinition时，我只看到两次声明@id和@name的数据类型的修改.另一方面，使用@ParamDefinition似乎可以更轻松地进行字符串构建".

So far I only see the overhad for declaring the data type of @id and @name twice, when using @ParmDefinition. On the other hand, the "string-building" seems a bit easier with @ParamDefinition.

推荐答案

避免使用字符串类型的代码-在这里，您必须将所有内容都转换为字符串，以便将其推入@SQLQueryInnen参数，然后引入一些问题，因为您必须弄清楚如何安全，明确地执行与字符串之间的转换，并将其转换回正确的原始数据类型.

You avoid having stringly-typed code - where you have to convert everything into a string so that you can shove it into the @SQLQueryInnen parameter, and then introduce issues because you have to work out how to safely and unambiguously perform the conversions to and from the strings back into the correct original data types.

对于int，转换问题不是很明显.但是，如果您查看人们报告的问题数量(在此以及其他论坛上)，他们遇到的问题在datetime和字符串之间转换，那么您会意识到这确实导致了真正的问题.最好始终将数据保留为其自然类型.

For ints, the conversion issues aren't very apparent. But if you look at the number of issues people report (here, and on other forums) where they have issues converting between datetimes and strings, you'll realise that it does cause real issues. Best to keep the data as its natural type throughout.

这篇关于在sp_executesql中使用@ParmDefinition有什么好处的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！