MSSQL为我们提供了两种动态执行SQL语句的命令,分别是EXEC和sp_executesql。通常,sp_executesql则更具有优势,它提供了输入输出接口,而EXEC没有。还有一个最大的好处就是利用sp_executesql,能够重用执行计划,这就大大提供了执行性能(对于这个我在后面的例子中会详加说明),还可以编写更安全的代码。EXEC在某些情况下会更灵活。除非您有令人信服的理由使用EXEC,否则尽量使用 sp_executesql.

EXEC的使用

EXEC命令有两种用法,一种是执行一个存储过程,另一种是执行一个动态的批处理。以下所讲的都是第二种用法。

下面先使用EXEC演示一个例子,代码1

Declare @SQL nvarchar(max);
Declare @DonatorId int = ;
set @SQL = 'SELECT * FROM dbo.Donators WHERE DonatorId = ' + CAST(@DonatorId as varchar());
EXEC(@SQL);
 
EXEC不提供接口
这里的接口是指,它不能执行一个包含一个带变量符的批处理,这里乍一听好像不明白,不要紧,我在下面有一个实例,您一看就知道什么意思.
Declare @SQL varchar(max);
Declare @DonatorId int = ;
set @SQL = 'SELECT * FROM dbo.Donators WHERE DonatorId = @DonatorId';
EXEC(@SQL);

关键就在SET @sql这一句话中,如果我们运行这个批处理,编译器就会产生一下错误

消息 ,级别 ,状态 ,第  行
必须声明标量变量 "@DonatorId"。

使用EXEC时,如果您想访问变量,必须把变量内容串联到动态构建的代码字符串中,如:

set @SQL = 'SELECT * FROM dbo.Donators WHERE DonatorId = ' + CAST(@DonatorId as varchar());

串联变量的内容也存在性能方面的弊端。SQL Server为每一个的查询字符串创建新的执行计划即使查询模式相同也是这样。为演示这一点,先清空缓存中的执行计划

DBCC FREEPROCCACHE (这个不是本文所涉及的内容,您可以查看MS的MSDN)

将代码1运行3次,分别对@OrderID 赋予下面3个值,2,3,4。然后使用下面的代码查询

SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cach%' AND sql NOT LIKE '%sys.%' 

点击F5运行,就会出现下面如图所示的查询结果:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAArcAAABrCAIAAADinD1dAAAdjklEQVR4nO2dy47kNpaG+TzeeVOQ9Qb1Bt52ZEL1EN7VzpFZgDazs59gMFWRbkC98TsMOi+1ETDd7na7Z1bOysq4ZhRiFrodkocUdU3S+X8gClkURR3ef1IKUnx/+R+niv/8rz+fAAAAAABOp9PpJKASAAAAAMAClQAAAAAAHqgEAAAAAPBAJQAAAACAR1UJcHBwcHBwcHCFk1TCPQAAAABABVQCAAAAAHigEgAAAADAg+8S4ODg4ODg4Hg3628cHh4eJo3fZ4JLe3AGU4I2HgAA/AEqYSaCS3twBlOCNh4AAPxhVpXw6dOnSeP3meDSHpzBlKCNBwAAf4BKmIng0h6cwZSgjQcAAH8IUCXkaRyn+Tx3jUd72p/bQoWgB9rOxnuW+QAA4AmzqoT7+/sRYhlLJRQ+cw0P7Wl/bgsVximsUeieA52Nh0oAAACOWVXC77//PkIs464lzDU8tKf9uS1UGKewRqF7DnQ2HioBAAA4VJXw9HTc7g5TuOPxy7//9/+0+H86EyVnV4ft7rCq/1/5qGHu3kXRu4szOczdu6j0+ObiTvE5XxX/jc7PIoNPEeHZT4VVqzMRnZ2r4dmnDEm7Txa2G3z3Lore3cp/315+UzwsuvxoeLpavlyq5WjV8v14UeUA80Tn3DabKkT10Ntpaj4cHBxcuE5VCYen42a7n8Idj1/+9du/5fg/LiOxWBV/Xy1EtLwlt9xeRtHljR5mdRmJyqcJU92r3XVzEYmzq81tc1fjE13elOH3m9vLSJyvyqecr24vo8qeMjzzlHHS7oOF7QbTCMu/rxaFPaVhtlKoypdLtRLtrVa+TRjlie65bTZ1dS6kR8DBwcHBNW4+lfDly5df//WbceChns0kj+u+DeMKIVquuDDUpxhlZf/VmVis9pvVOTN6laOy/JTbkdLugYV9DN5+XEZCCBFdfFQLTi8Fx1S3+MhPdDdeN9XyUDg4ODi4ymkq4fC03uymcMcvX/75629S/DeXUXR5LQW7WohoeUOu6mGoTx1GnH1oDcP6UP/VmVhcfVhEyxu3p4yVdg8s7GNwdenDQghx9sFeCl1TbfKhT3Q3Xje17RFwcHBwcOvN7llVwuZuGYnFqvi70AdXi7r3X52J6PJaD7PShysSphyrGp/rZSQWV2syhjU+6vBwtRBCFP/VwzNPGSftPljoYLBWNDeXUSnp7pZRtLyxlUJVvlqq2WhNQ7j6RGfj1RuJGNUfCgcHBwdXOVUl7A9Pj+vtFO54/PLLP37V4l8tqhXqxYft43p7vSTr1tHFtR7m+uJV6U//rsNE319Xl2gk9X+pT3RxXd549n69fVxv3y/Eq+VtFXMkxcA+ZUjafbLQxeCmaKKoyPbapzSJebpavmqq9WiZ8m1yQHtiZ+PLGz+cGR8KBwcHB1c5VSXs9ofPj5sp3PH45e+//HO6+Du76+WrV8u/Sp6rP4nFe+PV2dM+o4W+F9Z0xk+ZjXBwcHChO00l7A4Pn9dTuOPx+Le//2O6+Lu59wshhPjTB8Xn1ffX5X//unz1avnfz5j2eS30urAmNX7KbISDg4ML3akqYbvbf3p4nMI9HY//87dfpovfZxdc2oMz+A9jPBwcHJxXTlUJdwAAAAAAd3d3d3f63osTcjgcJo3fZ4JLe3AGU4I2HgAA/EFVCfcTI4R4sf8CAAAAYcGoBMcTIHpwf3//l/z0Mp0QYrqMnYJJa8LUBG08AAD4w9wqQQjx7AP2s7jgxq3gDKYEbTwAAPgD1hJmclhLmJOgjQcAAH/AWsJMLrhxKziDKUEbDwAA/uCuErJEJNmwh1nWEn78Lq63J379wxgD88/pV1+nP3YN83P6lRCSJS7xOLiWtYQsESJOc80/T+M4zYt/h2V+VywDbZZUGTS0RkyFTSXkaayYT3wafz3P2WCyd+HT5I9yoSZL1PKUI4/T3PQ4+QEkmiJ8EyhPY75OlRd71CvZpOcr/DyNm6fnaUxsyRK2+Or/msqazWpLEWhX58gNtk52KcG64vjacIGPuKiEujWMoBIEt5bw43exaEbi7PVAoVCM671VQu3jHs/gtYQsEUmijRwnH1UC6aGlztorrGK3ysrafDZ73XrkPI3JSFwNUc1/9ewhYws7jJl86v/Sh9DH52kcx80txXNGVwlN+GGlP6xKk7vlZJvKlKqE1rK2B+bvmiU3BqqEpu6PMOUDLwcf1hLyN1/Hb34eY/3ArgC6qoQ8ey2St3OsJeRpnGTMBNNHlUB5DrucMBrvKAhYTyaYPQMMTSZLRJKp19ofV9yhhasjytM4TtO6DmVJnCRG60ZQCcOKf2DVoQNenGZ1ZHJu9FQJdSwdVMIsuTF4LWHgbeBlwqiEp6fjdnfQ3E9n4nzF+Hdw/FqCaRhuFv+Tt2Ww5PXXQgjx1XfZm/KPnPqTkGQNoImn0iL0tYLzWsLbb5vVxfpNxJtviU/vtYRCJCgTpPphVW+eKquFTRhpuXWsFUUHlRDiWoI8+a/9+qmEls62i7B2XEvQs1yZPVdSUx48aWClXiWJslLI1CuTkWQ0pbdw1ZW+hUmy6gb1LUB9e2FVklZXtGyuHl1rJ2VYn3stYY7cyNj3Jj2Ge38bLvARRiUcno6b7V5zVwtxvmL8Ozh+LYFXCfmbr8uh98fvYvFt9pef06+KYZ7+UYzi1fDfhGxGd7JQ0fhUg/oPCaMSmvZLnqIb/HP6VS0OHNYbhHktoeri6m6HNOJizkRfvZaBmjDVX7Tpk3X1vrSqBG689QWr8bk69Kivnw3zSD3YuCpBN8DFKmXiWwbIEmapwFav9FrEDCVMhJJyKf9iqqsWiTSe67fXL1fqrOBNqdpOIY4MOpuWtilXdU+L//PkBtP2mYYufxRjeuMFjQDcmVslCMe1BMv3AcofSsj6HQEZy2lX8eYHLWbHdxDKCoRuYc+1BLkrUjp3fWXYxWcM7CrBZ4lwcvyNQ93DBrCWwElB+Yo0brJvJ1prkaJ72tObJewtbCTMMoaW+dyQKQyz5TyN5cl1nOYkw/uvJZAs7rGWMGFuWCPsBhYTQAc4lXB4Wm92mrtaiLMPjH8H1+G7hN4qgeqDUiUkb+0xO6mE7LWQ1yQ6qgRhWkuoVxLK/8gLxV6qBM8lwsn1l5DW189OmsCe6aO+cSgj08Jpb+LzNI5j9RWWGmG/EYiN0CkS+aNR93GxSiIz+5W/+M3TOElJU+qvEswfN9jvmiE3XMqobS3BdB8AJuZWCcxaAv8bB+6Ng0klVDrA8MaBvBcQyVvlXUYHlVCpjeI9xUhrCVxXk0mrjny/kKsrk9L8YMo3DsqH/F7i8AONMdYSVMHk8hsHA44Td+Uhub4cra8uMKlv6pX6exW9XplMoo/RFsmZcVGum/KCAX97kzq2hGTxkCXSqsMQldDn68Xpc6O5XStHk4Wavf7/Ogl4CKMS9oenx/VWc6uFOHvP+Hdw3fZLqFf4WXEgzeljJmQzcmev69cNyteLX8fubxyohT1UgmEtQRvO82rhtzQ5bl+0lEcp+xzCHdNAq2wG4OeMxGmzB+PrZ267guoFs5526i1n+7gqgfTspv0SWmWNXq/0guTrFfP+XloCU3JEq67SreVIKdTPArTbGVvkxBB/uSlZVAJb1mxWG0pcTfVcucG1/dimYzTqiuNnswV+wqiE3f7w+XEzhZtk78WRfqk4tQtuN8DgDKYEbTwAAPgDpxJ2h4fP6yncJOc4BKISBM5xmJGgjQcAAH9gVMJ2t//08DiFwzkOARGcwZSgjQcAAH9gVMLdZOBMyIAIeqAN2ngAAPAHdu/FqcBaQkAEZzAlaOMBAMAfGJUwNUKIF/svAAAAEBDupz2NwKSRe05waQ/OYErQxgMAgD9AJcxEcGkPzmBK0MYDAIA/QCXMRHBpD85gStDGAwCAP0AlzERwaQ/OYErQxgMAgD84qYSxNv619N3mbW774nKeiWUL29qSkc5FMabdtM/r1LSly1JYdX3wdit4q0qQNk7WN2K2BJ4G+RHEILLpvlCNbKu6gh4JLfsoe2wzyc4StWrIkTM7WNM4TBtIC20fY/akIvsRSkZKg6Sk1BE+Vysz2iadpJCTw6WbgzqcN5lmN5O2VZ6aZ8wf44biXDlyd1vq7yzN9gXRrhJGPCPE1Hdbj8zpjnvPYt/6vkMP1Y5NJVhOeJuO3iqh2S/f36Zo16PS+Q1SCqhP3RFpJzVbzg1in8cGo/5NRsqnBQ49stLestjiI90vO0SZfOr/2g6jiqWqLjiVwJ4v5dwu1Aj40x2H9WN9G6lim3xaBckaU7nbK16nwLpN8+ePtUZZe5ZezRYMoNsbh4GjmCHyacbG4SpBPzB+AE4qYc5xd8BagmsUz4fZeHZAVkJQb61Exkq02i93EQSsJxPMcYhgvNVr7Y8r7tDCyeNQmjTjQJwkzKnHc6iEYYU4kkqQ1DY9Lr7VZqf64Has5TPmT1+V0LPZggGwuyodt7sD5z5eROLsir3k5Pi+2zLZoutJeRrHSRIXk5wsjcnKZ+UvaMNgFui0k+b02Yx5LYGu09brganz2nuHtYQmReRwSFGdJackljfMcLs97Y4GU8t9bYq2hRClW5TWLssKZZ5wV6HriqFVAzqXzhJRjoVqMBKP9Bh58l8/s59KaOmvRz2yss4NXVTRVln15fLAqFmk1FKu2qv9A5sk0yjInHLZGKk0arl9yQVn7KOSVDvNUstuchR4oa+UYf3Z1hLmyJ8s5xpgq0ro2WzBIBiVcHg6brZ73d1cRCK6vOEuObouKqHpb/L6nPV6pGyGzKJjappVM5/J67dcdGEvpaezM7pUq3zG/lFZ5WqTz27fJcgJ1I3XE8sbZrjdnnZHg4nV3jZEy7stMn+T8oP2P7buRul/9WrQxEnWothgJP/lopK7TKmGSP21uerKHTFPR5WgG+BilTKpLQNkCbtCUIbVainJveqa1j+wSTKOgmpRkU7G3KjVgjP3UdIo2ySFrUlVfatrpKncuaPK7cVh8X/m/GnpkUq75C9oCp+ezRYMwFUlDJcI3VQC9VQqn95N05C0X47Vr3LKZQglZrsxJs2rW2jNaLc3DuYnssbrHQS/iOKWdkeDJUM9bYuuKkGavNYSjabJQSVoWdr0capmJcGMKoE82X3u6OijPmCstQR97D4pV6Qx0fB2gnlrYW/jmiXd1hJaH2Rv+Lbb82axU7OF5o08p47TnIRSbGafay8OU+Dnzx9rhJaq2bvZggFwKuHwtN7sqLteRiK6vJY9e7gO3yX0VglUH0hq2Byz3Za8mv0oKngmldBqvNmwrml3NNhquye4qARptqIvaJf0UQnVq/2q/+qnEuyvlp00geMQ4UDr45plADmcNg7laRzHMRvWVSW4jy729+5O0Vrbl7VMT2X1Mq0lFF5JQqJPUjIj7q8SLMO/bsAz5Y9LOXJrCT2bLRiAg0pYnQlx9mGwRDCrhEYRnk6n6iPpnFmtMqkEopu5Nw76ZVWJSqYYVQJZ1pxHJejG66mxGdYx7W4GMyb4R/t3Ca1vXLQbSlxUQtGLuWhZ+TFShg5fS+BbljlpFtofR96yS8vd8hBy0lcXTlocWi1l6pzWP7BJavmG39zJNLc4tS+mj9IKj8tuqiKkWsPkcCeVMOjrxenzp7md75HMVbNvswUDYFTC/vD0uN7W7v1CWrZ+tbylVzu5bvsl1F6sOKj/yNM4jpmQSpdN1/+amOP2rrbyoRbOpRI04/XE2g3rlHZHg5lIvcPlNw5FVrT1JXp3U6RefrFVR0i6uaarNE7CBHnb3ogXpR3IBVxlu+JZvTNWg7Ety5g0M+0qgQzWTRpICBdZo3+9WNRSmgaajdpD5A8CDZ9Dars2SJmllabavmjBmW/XreOyW9Hq8hBoUQlsfWCLw1ArjBk+V/4wySE9kq1q9my2oD+MStjtD58fN1O4SXbEcxihfWCctM+Y2KC3L3TbL+Gkd6Ij5W7HTgp9WsHgfDCuJXiAT7ZU+GjT6dRu10TNFvBwKmF3ePi8nsJBJQwFKsEN970Xx0dZN3a6wcuuen4ybe/FDpQjh6oSvBhDONt8wJf8ofiaVy8YRiVsd/tPD49TuKAHnoEEl/bgDKYEbTwAAPgDoxLuJuMl993BpT04gylBGw8AAP7A7r04FS+57w4u7cEZTAnaeAAA8AdGJQAAAAAA3N/fdzvtaSCTRu45waU9OIMpQRsPAAD+AJUwE8GlPTiDKUEbDwAA/gCVMBPBpT04gylBGw8AAP4AlTATwaU9OIMpQRsPAAD+4KQSMvlA8d5Y+m7zPrJ9cdmAiN01VtmpdKSNjFrGrSzhNzfR9/edC4vBY9WH6bDlNilidS9we7mzwbiqKx9Sw+VUlqjlKUfO7MFM4zBtgSy0zXVN1aZfvTLt4zs39OgGZcfe6hgJ5+2N2W2MbRVDsmLe3HDZ5doBx7MQAChwUAlNnRq6SZyp75Z3M1fOpOmOew9ob3WjjtB2lSAfDqfZ45VKGK8+TIc5t+Uj7Cwn4rj1yNaqy+YPGVvYYczkU//XdpxSLNVeMYFKYM4E6sWwKi0fmECSbSpTqhJay9oemL9rltwYRSVY1SMAOp3eOAwdrAyRTzMGDlcJ+iHrA2jL2CRjJpheqoQGfzfHNhrvKAhYTyaYPQcMKqo8V1qbmLY8Tj9WWX5InsZxmtZ1KEviJDFaN4JKGFb+A+sOlapxmukHLw5RCY4HKs6fGyOohDyNbfUCAB12V6Xjdndg3N27SJyv2Etuju+7TRW9mXTVLbY8TTxOszQmC7PKKeNKD9jEo53Xpk+2zGsJdBm5XtJMndfeW9bAlUVSxUL2WUr+nE7UxuFz/HaVMGz6NClm4+XJf+3XTyW09NGjHrpY10b9JGta7SupKQ+eNLBSr+S2c2LrlclIMpqqTVWtrnLzqW5QW6jS0pOUOfdRfnStnZRhfe61hDlyI8v1Pq3jssCYEx/wUmBUwuHpuNnuFXdzEQkhFivVv5ProhKa7jCvzyavT09ujlEu+s2mZ2imW2W0ytpkSk80Z17Q0eZHn6IbnJPXoQ7Nzv5Nhjz/0SxknqXljzRoj/Dm0a4ScuUluGe0rdzI/atU6FLnbv0uobW77agSdANcrFImvmWALGGWCmz1Sq9FjAhkIpSUC2mq5qahLmOYW7o07vKmVG2nEEcGnU1L25SruqfF/3lyw60Hkz+KkS0u8xMqAXTCVSVstvvN9uMyGiQUOqgE85xeVfpKyLrvJmO51FVkWsx2Y0z6XbfQmtH22a2wx9zVZwzc3jh4KhScfuNQ97Au80tHH/UBY60lcFJQviKNm+zbidZapOie9vRqM1NT5TyZm4/t9rxZMuQyTJ5cx2lOMtyU9vayJllsL9+5c8MaoQP2PADABKcSDk/rzY5118soWt6Zrra6Dt8l9FYJVB9Iyt4cs92WvJqcKYp+LJVQzYaaiHQd46lK8Le7cfslpPX1s5MmsGfAqG8cTPNA7U18nsZxrL7CUiPsNwKxETpFYm4+DjU5S7RZ8Un94jdP4yQlTam/SjB/3GC/a4bccCkj41pCrk2YfGy5wEPaVcL1MhKLq/Vmt97cLSOxWPWUCGaVIC2qVd9w58zKm0kl0Df1zBsH/TKJuYNKkJfzR1IJXFeTqRYyz9LyR5pmTvjGQc9QD3Ewfoy1BL7q1hdHVQn0TbzxNw71PJVVP1rNZ8pSr1cmk+hjDE315NJ8rC3dVkKyeMgSaeQbohL6fL04fW40t1t7MAe8FffATxiVsD88Pa631L1flPLz1fJWudTJddsvofZixUH9RzF10kM2TaFW16Q/LT3i9pGATNFa3gtYM9qQdm04z6uFX2oh+ywlf6SUTvv1opah3uFivPn1M7ddQfWCWZ+NMVW3edSIKoGM2qb9Elwqs1Kv9KklX6+Y9/fSEhjbVE/m5lOmQY5Wu52xRU4M8ZebkkUlsGXNZrWhxNVUz5UbXNvXejAHoBJAJxiVsNsfPj9upnCT7IgXSJUPbjfA4AymBG08AAD4A6cSdoeHz+spHFRCQARnMCVo4wEAwB8YlbDd7T89PE7hXnLfHVzagzOYErTxAADgD4xKuJuMl9x3B5f24AymBG08AAD4A7v34lS85L47uLQHZzAlaOMBAMAfGJUAAAAAAHB/f9/ptKehTBq55wSX9uAMpgRtPAAA+ANUwkwEl/bgDKYEbTwAAPgDVMJMBJf24AymBG08AAD4A1TCTASX9uAMpgRtPAAA+EMHlTD8bABL5OZtbvvistuSZQvb2pKRdm1qGbeyxLgLbTz+SU4utA20XbYfnh2b8aSI1b3A7eXOBuOqrnzgDlens0QtTzlyZotoGodph2Z9o2BTtelXr0x7Es+NdMRETk5mbk65cN6hmd2J2VYxJCvmzQ2XTbitjLmFO3gxOKsEa5/jiClyeUN25cic7rj3gPZWN+oIbR905fPtNHv8Ugl17+hvT2M2Xj6Ob+iZkPaqywopMraww5jJp/6v7bSnWKq9YgKVwJxv1IthVVo+6oEk21Sm+ikwFmPsgfm7ZsmNYSqBmDjMWvDCcFQJeRrHSTJ0qLJEPv4YOFwl6AfGD8CqEvI0TjJmgumnSigIcy3BURCwnkwwe8EYsihLRJKp19ofV9yhhZOPOU7rOpQlthY7gkoY1nAHVulG7GUJPWvddOhzJ5XQ50zIWXJj8FrC4PvAS4TdVem43R0kd3Uuzn66vfwmuvyoXuri+L7bVGGbSVfdYpMkLuZgWRqThdnKX9C2zawxaifN6ZMt81oCXUaulzTThPhYaVkDVxZJFQvZZyn5czrNdiZk9awAVYIy+a/9+qmElr521DMh69qoxKnMniupKQ+eNLBSr+S2c2LrlclI/azIxhKlusrNp7pBbaFKS09S45mQ5BztQjspw/rcawlz5EaW631avwVerCWADjAq4fB03Gz3xF0txPlqu7+5iKKLj/Klbq6LSmjqcV6fs140CPpH2W82PUMz3SqjVdYmU3o6O/OpBW1+9Cm6wTl5HeqgzO3fZMjzH81C5lla/kgtf/g3JH9UlXAiZcxpMrlzt36X0LrO1FEl6Aa4WKVMfMsAWcIsFdjqlV6LmJGEiVBSLqSpmpuGuoxhbunSuMubUrWdQhwZdDYtbVOu6p4W/+fJDbceTP4ohq1/nFIGwEi7SqjFwawqwTynV5W+ErLuu8lYLnUVmRaz3RiTftcttGa0w2t+c8xdfcbgj6sSKuoe1mV+6eijPmCstQROCspXpHGTfTvRWosU3dOeXu2VnKlynszNx3Z73iwZchkmT67jNCcZbkp7e1mTLLaX79y5YY3QGUgE0BVOJRye1ptd5e6WkTTMRss7crWb6/BdQm+VQPWBpOzNMdttyavJmaLox1IJ1WyoiUjXMVAJHXH7JaT19bOTJrBn+qhvHMrItHDam/g8jeNYfYWlRthvBGIjdIrE3HwcanKWcLNi+YvfPI2TlDSl/irB/HGD/a4ZcsOljKxrCZAIoAetKqFx18toiEQwqwSl7hbfcOfMyptJJShf7kptib1MYu6gEuTl/JFUAtfVZKqFzLO0/JGmmXjjYK5p9CsO95HDEIyruvXFUVUCfRNv/I1DPU9l1Y9W8/XGwdQrk0n0MYamenJpPtaWbishWTxkibTqMEQl9Pl6cfrcaG639mAWBv96DLxMGJWwPzw9rre6u15Gr5a37CVH122/hNqLFQf1H8XUSQ/ZtK5aXZP+tPSI20cCMkVreS9gzWhD2rXhPK8WfqmF7LOU/JFSiq8Xbdt+qJkkFW2Vo4pn9YJZDcZW3eZRI6oEMmqb9ktwqcxKvRJaTHy9Yt7fS0tgbFM9mZtPmQY5Wu12xhY5McRfbkoWlcCWNZvVhhJXUz1XbnBtX+vBzCjbeGBNATjCqITd/vD5cTOFm2RHPIcR2geC2w0wOIMpQRsPAAD+wKmE3eHh83oKB5UQEMEZTAnaeAAA8AdGJWx3+08Pj1O4l9x3B5f24AymBG08AAD4A6MS7ibjJffdwaU9OIMpQRsPAAD+wO69OBUvue8OLu3BGUwJ2ngAAPAHRiUAAAAAANzf33c4OXo4k0buOcGlPTiDKUEbDwAA/gCVMBPBpT04gylBGw8AAP7w/6lyYMJFMiIGAAAAAElFTkSuQmCC" alt="" />

我们可以看到,每执行一次都要产生一次的编译,执行计划没有得到充分重用。

EXEC除了不支持动态批处理中的输入参数外,他也不支持输出参数。默认情况下,EXEC把查询的输出返回给调用者。例如下面代码返回Orders表中所有的记录数

DECLARE @SQL NVARCHAR(max);
SET @SQL = 'SELECT COUNT(DonatorId) FROM DonatorS';
EXEC(@SQL);

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAk0AAABjCAIAAAAJoaNgAAAKGUlEQVR4nO3d327TWALH8fMkSDwAV15G7bwBD8AVWiFa5FdA3K4E2s70Ym5a5AtWuUCVRsyWkpXgZnfISox2tjEUCjRsIcOfMqVN08TOnzZNU3kvThsc+9hOSVM7nu9HP0WOc+IafPHTcZxYWJaVyWQ+9239SKlUmp2dbTab9XqjXq/Xal9i2zXbrrnXEEIIIbFEyJ7b7FUqleRCpVKxbbtSqWz6bG9v37p1q9Fo1ut127YtAACSJ6znZMldvHjRtu2gnqvXG5ZlVyrVcnmbEEIISVoCe06W3NmzZ69duxbac/VK1SqXt0ulLUIIISRpUfecLLkzZ85cuHDh9u3bIT1Xq9W3tyul0tbGxiYhhBCStKh7rlarnTt37vz589evX5+fnw/puc3N0vv3H1ZX366svCaEEEKSlrD53KVLl6anpx8+fBjScxsbm8Xiu0Lhf8vLrwghhJCkJeLzuZs3bz569Cik5z5/3lhdffvixcrTp88JIYSQpCX6essHDx6E9Nz6+udCYXVpaXlx8QkhhBCStAz6/bmPa5+ePn3+yy//ffz4P4QQQkjSEtFzIWTPvX37Wy73+Oef//3Pf+UIIYSQpOWw58p92zpSrVbv3r27uJh/9uzFkyfPTPMpIYQQkrQc9lylV7VarUSxLGtpaWl+fv5vAAAk1WHPlQAASKPDngMAIJVEHD8eDQDAKaHnAABpRs8BANKMngMApJmwLGtqemZEE/f/HgAg6Q57zhlB9BwAINJo91zyE/fxBYA/utHuuU6y0XMAELuIntMNUxlN9+Y0G06amp6p9crlcrlcLtsrk7lTiwM9BwBJEN1zhukcxnD0gIT3nBAi5OkJ9pxheHejVCrphkHPAcAf1rHnc/6ZnKabmjZQz4lg4T1n9+r2nG3bZtkxy87coqXrhh2H/f19eg4AYhfRc+4ZUj6f7y7ncjm50Gq1DMPUND2y5EJqzN1nQcvKnvP8Y+TeFgoF0zTz+fzcomUYpq4bA/0HicCvGIa8ZFkWPQcASXCMnut2m+M42WxWlpycRY2Pj0eWXEh7ffV8rtrLMEzDNLPZrGGYmcwdGV03qn0TQvSzEMS9KXoOAJIgoucymTuFQiGfz+d6yS6RV3mE95y/rpQ9F7ms7LntXv7P5+YWrcOJnWkaplksFrejCCHcC+5Hz4CgN0rtdpueA4DYRfdcd9k9nzMMU34KZZadTOZOUM956iporvbV8znPvc4Nw1xbW5M3HDJNZ27Rmlu03PusaXqxWOz/5unlclkI4V8TxD2MngOAJPia85atViubzXav9QjvufCuGmT81PTMVi/DMIvFYs+k0zTlP8MxdMMw8/m8rLotFSGEe9nPPyzo7VtbW/QcACRBRM/Nzs6apilro/vRl3zsfgZmGEbkeUvZXsq5Wsj0KHxKNzU9U+zlP2952HOO4zhOsVjUdF3TdFl1SkIIz4LypSDu8Ts7O/QcAMQuuue+dIZhygtPbNvOZO6YZUeeG5ydnQ253tJTe8pl9xr3Y7ip6Zk3vWTPyYlmqVQyy0c9Z+iOrjuOoxuaYZiarr+Joqyx7ktBb3E/pecAIAmO0XPyXGX3M7nuB2CRPeeezwX1nPBdmRlZdVPTM6u9uj0nS84s95x3tW27WCwapqlp+moUIUTQmpD5nHs8PQcASRDdc92rK93nKuVllrNHgnrOc+JReULSPybo7f6ee91Ltlq35EzzqOd03dG1tbU13dBkz70OJoSQj35Bg5WazSY9BwCxi+658fFxTdM9j+4FKWgLntpTLgeNCTc1PbPSq6fnTEc3te71oqVSqVAo5HI5edXlSgAhhGch6CX/SM9b6DkASILRvl/By17yikr5bXGZw57TNUfX8vm8pmuy516qCCGUy541csEzwL+y0WjQcwAQu9HuueVe8hqTy5cva7ouI3vu8HviR+ddNU1fjiKE8DyVa9zrPWc1Pa/ScwCQBKPdc8+jyFbzJ/KNg6vX6/QcAMRutHtuKcHoOQBIgtHuOUIIISQ8I9xzAABE8vbc3b//gxBCCElNFD031F6t1WpD3f6JG7kdBgC40XMRRm6HAQBup91ztm0PdfsnbuR2GADglqqe8/9mWP+/IhaEngOAkZaQnnugC/1BHzcWD1rffdWz3YCeO/xzA+wwAGA0nHbPWZblX/nmh2/9tRMyFQuqN//6wF7su+mUOwwAGBWn3XPVatW3Tt05A/ac8u2ulcpu7XOHAQAjQ9Fz+/ud3VZ7GOl0Dj5vbHq3f/+qmMz6Bwshgrbjnp+5R3oWPFvwPF2YFJP3+9rhGA8PAGBAip5r73d2dveGkU7n4Pf1z57tv/h+bOz7lZ3dvZAP5yQ5vrvQXVa+5Bngf6n7dyN3OMbDAwAY0Kn23MHBwaff14N6TtlP4ev76Tn/yv57Tu5wjIcHADAgVc+195s7rWGkc3Cw9mnds/3l78bGvnvlGSmE6D4q4x7QHeYeHzIvDPm7yh2O8fAAAAYUf881FybFxH1/h4X3nHJYt/ZCarK78t6EmFjoa4djPDwAgAEpem6vvd9o7g4jnc7Bh4+ffNtfmBCT80dPhRCNqGX/0+OPeTk19uWPhu9wjIcHADAgRc+19tr1xs4w0ukcvP+w5t/+86mxK/M79caOEMK93v00aDlovHyqVG/s1OcnxMRCnzsc4+EBAAxI1XOtdq3eHEY6nc679x9V2793RUwIIfxvcfdTd03IMM9K5Uj5537qe4djPDwAgAEpem63tWfXGsPIfqfz27sPw9v+kHY4xsMDABiQoudeoVeMhwcAMCDl76EMUbvdHur2T1y73Y7x8AAABqToOWvIhBAj9BjjsQEADE7dc8P7e5ZlbWzXRiVi4NvXAQDiFUPPCSFiL7A+w3wOAEYd8znmcwCQZsznmM8BQJodq+f6vgl3sJD53Nzloy97X/4p9oZjPgcA6dBnz7354VtZQSfQc8r53K83vjmqt6W/nBd//jH+kmM+BwApkKD5nLvz/nRjKfaSYz4HACmg7rn9/c5uq+1LdlJcXVCsP0b6+HyO+RwA4MSoey7gluL3J8TVhcFuKR45n/v1xjfi/F9/jbvhmM8BQDrE0HMh87lElRzzOQBIgYCeU99S/P6EmLw32C3FQ+ZzSSs55nMAkAIx9Jx6PvfjFSGuzMVdbMznACBl1D23195vNHd9WZgQk/OK9cdI0Hzuy5fnhBBCcL0lAOBEqHuutdeuN3aGEX4PBQBwmgJ6rtWu1ZvDCL9vCQA4Teqe223t2bXGMMJ8DgBwmtQ992pomM8BAE5T0O+hDAvzOQDAaVL33LAJIUblMcZjAwAYnKLnYtwbAABOFj0HAEgzeg4AkGb0HAAgzeg5AECa0XMAgDSj5wAAaUbPAQDSjJ4DAKQZPQcASDN6DgCQZvQcACDN6DkAQJrRcwCANKPnAABpRs8BANKMngMApBk9BwBIs/8Dl4cvG+p0aW8AAAAASUVORK5CYII=" alt="" />

然而,如果你要把输出返回给调用批处理中的变量,事情就没有那么简单了。为此,你必须使用INSERT EXEC语法把输出插入到一个目标表中,然后从这表中获取值后赋给该变量,就像这样:

DECLARE @SQL NVARCHAR(max);
DECLARE @RecordCount INT;
SET @SQL = 'SELECT COUNT(DonatorId) FROM DonatorS'; CREATE TABLE #T(TID INT);
INSERT INTO #T EXEC(@SQL);
SET @RecordCount = (SELECT TID FROM #T);
SELECT @RecordCount;
DROP TABLE #T;

sp_executesql的使用

sp_executesql命令在SQL Server中引入的比EXEC命令晚一些,它主要为重用执行计划提供更好的支持。

为了和EXEC作一个鲜明的对比,我们看看如果用代码1的代码,把EXEC换成sp_executesql,看看是否得到我们所期望的结果

Declare @SQL nvarchar(max);
Declare @DonatorId int = ;
set @SQL = 'SELECT * FROM dbo.Donators WHERE DonatorId = ' + CAST(@DonatorId as varchar());
EXEC sp_executesql @SQL;

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAkYAAABcCAIAAAAEbe3vAAAPNUlEQVR4nO2d3W7bRhqG50oK9AJ6pI1B+w56AT0qFkElg7dQ9HSBFmtZAXziFDzowgeBgSK7ruwF1JPdVgu02NZi40b+68ZRm9RpYst/kh3/yTK4ByORw+HMiPqhOSLfBy8MihwOP3+fOK+Hokyyz1Cv1/f394+Pj5vN5gcffNBsNvcDHB0dff7552dnbxuN5vHxyeHhEQRBEATpIMJZGvWzd9999+OPP1Za2tnxSePw8KheP4AgCIIgHeSzNOpn77zzzvvvv//FF18oLO309Ozo6LheP9jb24cgCIIgHeSztNPT0/fee+/evXuffPLJ0tKSwtL29+svXrx89uz51tYvEARBEKSDBLO0Dz/8sFAofP311wpL29vbr9V+297+X7W6CUEQBEE6SPxZ2mefffbtt98qLO3Nm71nz56vr289efIUgiAIgnQQb2nuXK1UKiks7fXrN9vbz9bWqqurP0EQBEGQDhJYmutqx8fHMkv7fffVkydPv//+x++++y8EQRAE6SCxpSmglvb8+a/l8nfffPOff/27DEEQBEE6iByG46DLycnJ48ePV1crP/+8/tNPP9v2EwiCIAjSQeSY4eTk5LgXjUZjbW1taWnpbwAAAIBOkDoAAACQCMgCAAAAkAhIAwAAAEgEsDQAAAAJAZYGAAAgIcDSAAAAJAQyU5gfO8WdNAAAADpCnHEDlgYAAEDIWFqa/oq7rAAAkEbG0tLaegNLAwCAWBBbmmnZQmVMXnfsZ47jzBTmT/2Uy+VyubziZ2Hh0WkcwNIAACAupJZm2U5HlmNKpLY0Qoji5QgtzbL4MOr1umlZsDQAAEgVYWdpwflZxrQzmaEsjchRW1rTj2tpzWbTPnTsQ2dxtWGaVjMObm5uYGkAABALYvNg5z2VSsVdLpfLdOHq6sqy7EzG7OlnCsdirUu2LLQ07neg0W5vb9u2XalUFlcblmWbpjVUXoj0G3uKTY1GA5YGAABx0dvSXBtzHGdlZYX6GZ0bTU5O9vQzhVENPEs78WNZtmXbKysrlmUvLDyiMk3rJDSEkDALMtiuYGkAABAXYvNYWHi0vb1dqVTKfqht0Jsv1JYWdCahpfVcFlrakZ/gZ2mLq43OdM22Lduu1WpHvSCEsAvsT66BbEdKq9WCpQEAQCxILc1dZmdplmXTT4zsQ2dh4ZHM0jhnks3ABp6lcU/ctix7d3eXPizHtp3F1cbiaoONOZMxa7VayOd3Uwjhn/etiJZtBksDAIC46OPC49XV1crKinsLhtrS1LY0TPuZwvyBH8uya7Wabypp2/TXcCzTsuxKpUJd7UAEIYRdDhJsJtv94OAAlgYAAHEhNpKHDx/atk0dwv2Yiv50P6+yLKvnhUdqVMIZmGLSo56ozRTma36CFx47luY4juPUarWMaWYyJnU1IYQQbkG4SQbb/uLiApYGAACxILU0zx4sm94P0mw2FxYe2YcOvbj38OFDxR2PnMMJl9k17E81M4X5HT/U0uj0sV6v24ddS7NMxzQdxzGtjGXZGdPc6YXQsdxNsl3Yl7A0AACIi96WRi82up+fuR9W9bQ0dpYmszQSuDeyp6vNFOaf+XEtjfqZfei7cNpsNmu1mmXbmYz5rBeEENkaxSyNbQ9LAwCAuJBamnt/I3uxkd7o+LCLzNK4K4fCK4rBNrLdg5b2ix9qYK6f2XbX0kzTMTO7u7umlaGW9oscQgj9GUTWWMj5+TksDQAAYkFqaZOTk5mMyf1kFygy4+EcTrgsa6NmpjC/5cdnabZj2hn3js16vb69vV0ul+l9j1sSCCHcgmxTsCW3CywNAADiYiz/E/+GH3pPI/3CNVXH0syMY2YqlUrGzFBL2xBBCBEuc2voAtcguPLt27ewNAAAiIWxtLSqH3rrx/379zOmSUUtrfNV6+6F00zGrPaCEMK9pGvY9dxlSW4rLA0AAOJiLC3taS+ogQXVc8fhOTs7g6UBAEAsjKWlrWkMLA0AAOJiLC0NgiAIgoIaP0sDAAAAhHQs7fE//glBEARBYy3P0iJ1ztPT00j7jw6dI9c2Nm0DSzmoi4MkJB1YWg90jlzb2LQNLOWgLg6SkHTuyNKazWak/UeHzpFrG5u2gaUc1MVBEpIOLK0HOkeubWzaBpZyUBcHSUg68VnaztwU8z84zFKkx+8ecWpuR70mgCRyZrcQnUSE9OQsmYTEFJPjOPqU+46PqD061KVkBv4v+GBHHeh0dmBpSeeOLK3RaPCrfO+/nbmpIU6nkKYy0DkgjpwwlhGfpQlicxzHcUomMU0zkqDC/bL6lVt+xPjKd/doUZcOJXNIAx3U0mRnDUgGd2RpJycn/Cru/TfMyBKlpckin3PPyPjGREFsjuM4O3NTZskpReJp4X5ZHcst2ytNlqZFXTrEZmmSswYkBM/Sbm7al1etKNRu377Z2+f733xgGA82vDUrOTJdpOs71yO6L40Hs7nOqtxy6/KqVcx5Fy1yy1uzdAfam2D36ZxBCJkuukf02nT3GiBytjfjwQYfFXtoYhRWZjsLW5e+ACZnN0eaVdp5buXyamvWmJzdDBmGl+qNwO/IJN+f6nEpt/+I4XobvC7aSpe6cAei/eRWLrvLRm7afcd67YOl6f90dpMQ75gLIsWztNZN++LyOgq127d/vH7D979RMIzCurdmK28Y+Y2tvEGyxeuLy+v1WYPkli82CgbprAns0l3jrRfubuQ32N29NhfFacJ12E/kxRzJFpVR0UOzC50AuJBGl9XL6/VZw82AMbt1oQojkCs3GO9X8Cc/RMCalZs7orq3EdRFW+lSl46Ws2S66O7eWV7Okuki07izu7A0/Z/ObhLiHXNBpNyFpd3e3r7643Wvc6n7bg6Oqtyazjng/l3GtAmzu7DDASNfznJHV0fF+AQDd6oPl9XLrTzbuzqMAXIVYqzXq9zcEdW9jaIu2kqXunDluLy+cP86LE4L/rTqmFygNP2fzm4S4h1zQaQwlta6Ob+4ikLt29vdV6/5/tcLhlGoci/ZlbI1F8tZYuTXRW3C7C7ocPDIq3nDyObCRuWuJ7mvostqdrn7cjNvGPmiPIwBctUrY9qVmw9A2dso6qKtdKlLR8tZNtvFHMkuf5U18uuS3YOl6f90dpMQ75gLIkUTS9vMGyRbZBeuqnmDZJcF72/2ZCjmiO9tHWZ3f5vhLO38YjlLCFFHxS94AQwzjApjq+YNI7/pe9lxXFUYnVypEjtaS7vLcnNHDNVbIu1Nl7p05Lc07zzyJb/7zhSVpv/T+RyWlgI8S7tu3bw9v4xC7fbty99f8f1XZyeYSwnZrwLrjdkqfUkXmOUqe3HNmK2eF7OEEJJbCrc708bwtvYVObNXNW8QYVTs4fhgaMCEEGOmOsKsFrNch/Q3lYXB5Yr9FWhmBNljUq1/uUVHVPU2irpoK13q4r1Xfe+ipSyZyG90+ze4d6agNP2fzm4S4h1zQaR4lnZ13Tp7exGF2u3bFy93o+s/OukcubaxaRtYyqV3XYofkewSXa7mJybyT6NMQrxjLogUxtKuWqdn51Go3W7/9uL36PqPTjpHrm1s2gaWculbl6UsIWRiptp5+TQ/MZFfizIJ8Y65IFI8S7u8um6evo1CN+32r7+9jK7/6KRz5NrGpm1gKRfq4iYh3jEXRIpnaZsAAJAC4h1zQaSw/z0kQlqtVqT9R4fOkWsbm7aBpRzU5ebmptVqxTvmgkjxLK0RMYSQMf0JAEgM8Q64IGp8lhbdYRqNxt7R6TiKEBJdWoZE2/NT28BSDuqCDCSeu7M0Qkjs/jSAdD4HtI1N28BSDuqCDCQezNIwSxs92gaWclAXZCDxYJY27CyN9byg/9E17nruZZg+1VkN0+zu0TawlIO6IAOJJ4ylDf2wPuUsbfF+93/q3P977AbW7yyN2yq0NBfuJetzwTUhs6rc7itcyeweI1DM4CZF4yEDC30s/7vObRQieHd98HmQXGP6fHIGPoLwaYm3cch69VWXRAJLSzxqS3NP+RFYGhHN0n74dKLrZGt/uUf+/GX8HhZ+lkb88zMWrgERzdWC/cgaKLIq2RIonPfA38Cjf4ObFI3DIQtsZ26KeRY4MUvCYwXfdSWz+4I3KlmoO3NThLc0wdG5zeyqvtISb+PQ9eqjLgkFlpZ44p+lsfb2p0/XYvewkLM0of0EG3M+F9L5gm1kWVVmnSmceFGySdE4HGEGjs7oKz2Wf4NsoBbvvjM3NWWaqtE9OPaXTP+KvtISb+PQ9eqjLgkFlpZ4fJbGP8Q98Ej1gRXis7Txm6U5EhPirEj90lFeeByVpbHjFDdmBTcpGockxMDRmQ3Ij+ULXjZOi3cvmcQsKSMPdKmYHanS0gkygsZ9hBG+XuHrklRgaYnHZ2mSB1v7nj87mHrO0n74dILc++sPcRtY+FmaIzInR3JRkZ2lOfLrkOmxtJ25KcJdM1Na2pRpCi+Bh3QO2dHZNdw4PnLjia7xCC0tmJmEAUtLPHdnaYpZmrZ+ppilceYknKUFryiyu6fW0thxM+wszfusiJil7r0M/qkYF7y74DYOHj14LDbItFla4v3MgaWlAL+liR9szT1/dhApZmk6+5l6lubI79pnX8oQtuE6GZWl6fNZGj9uhvssTTpk87vvcPcwCu4QCYzaYhtI2WdpafAzB5aWAu7O0sSztC8/IuSjxbh9a4BZWid9vUyIsy71coSWNqob8MIhDcy7dVF+9GDw3rLiMzA+VEHowaNLGop7HlUOR944dL36qEtCgaUlHp+l8Q9xlzxSfQDJZmnel9IIIYSM0R2PnfQFnGlgS5NN4BRHH+57ad7WO/temtctM4WK6HtpwdFdeHTFvCZEWvrKYXSNh/1emjgzSQSWlnh8lhbdQ9yT+t9DgpYjNCE6UjiMb3GbSAjnC6Lt+altYCkHdUEGEo/f0iJ7iHtS/8djT3Pi1nCbFB3KGrBoe35qG1jKQV2QgcTjs7ToHuKe1FlavGgbm7aBpRzUBRlIPD5Li+7J6EmdpcWLtuentoGlHNQFGUg83H8PiQrM0qJA29i0DSzloC7IQOLxWVrUEELG9CcAIAHEO9qCO8CztHjjAAAAAIYElgYAACAhwNIAAAAkBFgaAACAhABLAwAAkBBgaQAAABICLA0AAEBCgKUBAABICLA0AAAACQGWBgAAICHA0gAAACQEWBoAAICEAEsDAACQEP4PGm9ah9+BQDwAAAAASUVORK5CYII=" alt="" />

注意最后一行;事实证明可以运行;

sp_executesql提供接口

sp_executesql命令比EXEC命令更灵活,因为它提供一个接口,该接口既支持输入参数也支持输出参数。这功能使你可以创建带参数的查询字符串,这样就可以比EXEC更好的重用执行计划。它的构成包括: 代码块,参数声明部分,参数赋值部分。说了这么多,还是看看它的语法吧

EXEC sp_executesql

@stmt = <statement>, --设定动态执行的SQL语句

@params = <params>, --声明参数

<params assignment> --参数赋值

为了说明sp_executesql对执行计划的管理优于EXEC,我将使用前面讨论EXEC时用到的代码。

Declare @SQL nvarchar(max);
Declare @DonatorId int = ;
Declare @DonatorName varchar();
set @SQL = 'SELECT * FROM dbo.Donators WHERE DonatorId = @id';
EXEC sp_executesql
@stmt = @SQL,
@params = N'@id as int',
@id = @DonatorId;

在调用该代码和检查它生成的执行计划前,先清空缓存中的执行计划;

DBCC FREEPROCCACHE

将上面的动态代码执行3次,每次执行都赋予@OrderID 不同的值,然后查询 sys.syscacheobjects 表,并注意它的输出,优化器只创建了一个备用计划,而且该计划被重用的3次

SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cache%' AND sql NOT LIKE '%sys.%' AND sql NOT LIKE '%sp_executesql%'
点击F5运行,就会出现如下表所示的结果;
aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAA3kAAABOCAIAAAASBUgKAAAShElEQVR4nO2dW0/bWL+H1yeZD4A0FyiaKuUb8A2Q5mL0hsp8hHePesfFSKWpxL5oR5bKFhdVb0YbAluiNwzDlubV6AVPOwUK3U3ptEOnh/RAQjkGquwLx86yvZYPIU5i+jz6CZmV5eX/Otjrx7IdxFuHjx8/1mq1r776anh4eGpqqlarvQ3w4cOHH3/8cW/v04cPHyuVd2/evEUIIYQQ0qlarf7HP/85Nzfn21haWqpWqz0PD3VBwvWRe3t7X3/99TfffPP999/PzMyEeM23byvPn7948uTp5uZjhBBCCCGdXr7827aYvo2lpaWXL//ueXioC2p5TXtd89tvvy0Wi/fu3Qvxmm/evN3e/nNr6//W1h4hhBBCCOn04sWObTF9G0tLSy9e7PQ8PNQFtbxmpVKx7eYPP/zwyy+/hHjN16/fPHnydH198/79hwghhBBCOj179vw/Jyd//vln38Zvv/327NnznoeHuiCP13RXNxcWFkK85qtXr7e2njx4sLay8jtCCCGEkE5Pnz4rzc6urKz4Nh4+fPj06bOeh4e6IL/XdO3mx48fdV7zr52X9+8//Ne//v3rr78hhBBCCOn04MHa48flx4/Lyo2eh4e6IIXXDMH2mk+fPlte/nVp6X8Xf15GCCGEEEJIJ/E+Bu8cdnd3f/rpp5WV1T/+WP/99z8s6z5CCCGEEEI6iY8Ou7u7H6OoVqsPHjyYmZn5LwAAAACAKEQFAAAAACAdxDQAAAAAQDqIKgAAAABAOuA1AQAAACAt8JoAAAAAkBZ4TQAAAABIC3GteDNb6nWLAQAAAEBcRCNT4DUBAAAAMkT2vGb/q9d9CgAAANAvZM9rnvU3eE0AAAAAF4XXNExLqZzhV0+85p6X5eXl5eXleS/T03f2egFeEwAAAEBG7TVNq9GU2TA0CveaQoiQXzvoNU3TH0alUjFME68JAAAA0HNirWsGVzRzhpXLnctrCj3hXrPmxfWatVrNet+w3jfurlQNw6z1gtPTU7wmAAAAgIvC2Mkrhaurq+728vKyvXF8fGyaVi5nRBrNECspe0rdttJr+ipgR7u1tWVZ1urq6t2VqmlahmGeq1GE9mtHQz6qVqt4TQAAAACZCK/p+stGozE/P28bTXs18fLly5FGM8RBtr2uuevFNC3Tsubn503Tmp6+Y8swzN3YCCHibOiQi8JrAgAAAMgojN309J2tra3V1dVlL7afs9+8CfeaQcuo9JqR20qv+cFL8HnNuyvV5gKnZZmWtb29/SEKIYS8If/0ZdDtaFOv1/GaAAAAAC5qr+luy+uapmnZTyVa7xvT03d0XtNnGXVrlm2va773YprWzs5OpVKpVCqW1bi7Ur27UpVjzuWM7e3t90kQQgRTdMjZ8JoAAAAAMnHvoR8fH8/Pz7vv34R7zXC/eJ7814o333kxTWt7e9uz+GpZdjUapmGa1urqqm0336kQQsjbQYLZdLu/e/cOrwkAAAAgozB5t27dsizLtm7uo5D2T/eZSNM0I++h2w5SuWYZskwYvrR5rXhz20vwHnrTazYajUZje3s7Zxi5nGHbTSVCCN+G8iMdcv7Dw0O8JgAAAICL2mu2fJtp2S8D1Wq16ek71vuGfZ/61q1bIe+h+6yncltOkX+Gc614s+zF9pr2gmulUrHeO17TNBqG0Wg0DDNnmlbOMMpRKK2k+5FuF/lXvCYAAACATITXtO+bu89oug9ERnpNeV1T5zVF4I31SLt5rXjziRfXa9pG03rveQagVqttb2+blpXLGU+iEELoUkLWNeX8eE0AAAAAGbXXdN86l++b26+f33LQeU3fTXDlzfFgHt3uQa/52IvtLF2jaVmO1zSMhpHb2dkxzJztNR/rEULYP4PoMis5ODjAawIAAAC4qL3m5cuXcznD91PesNE5Qp/1VG7r8oRzrXhz04vHa1oNw8q579FXKpWtra3l5WX7bfRNDUII34buo2BO3y54TQAAAACZzvyb8q5xrXhzw4v9prn9je62ml7TyDWM3Orqas7I2V5zQ4UQQrntS7E3fBmCifv7+3hNAAAAAJfsec01L/Z7P999913OMGzZXrP5Xe7OMwC5nLEWhRDC96udIqf77rD7PsVrAgAAAMhkz2s+jMJ2lkFF7nh+Pn36hNcEAAAAcMme13zQx+A1AQAAAGSy5zURQgghhFBWlDGvCQAAAAAZQjQajZ/++38QQgghhBDquJpeM1U/u7e3l2r5/Uzm6p65gGUyHTwAAEC2iDnt4jXTJXN1z1zAMpkOHgAAIFv0kdes1Wqplt/PZK7umQtYJtPBAwAAZIuY0y5eM10yV/fMBSyT6eABAACyxYXzmuXJoaHJcnf26hzRde91hD4ybdcSB99njQ8AAJAh+shrVqvVDpTSKa9pp3TLZETXvdcR+uhMZ3WE5C2QOHi8JgAAQLvEnHa74TV3d3c7UEpn1zW7ZTKi697rCH10prM6QvIWSBw8XhMAAKBdYk67Ta95enp2dFxPQ2dnn1+/eRsof35UNBmdqx8d10vu706KP8+jG/n8jeuj3jyPbuSbCZevP/KlXCnZv+avjOY1KXaBo/N2VKVRkR+94s+vPMp56t5PEUYH/OhGPn9jw7u9UbxsHyxf3NQc3d+/qlp7i/X37+Z1pwUUR4zd2vpQhXAOupHOyEcIIYQusOxpN4HXrJ+eHR6dpKGzs89/v3rtLX9zIi8KJXt7riDyExvSLhvFfL64HsxTKuaFk9LK4+wb2Gv9el6Mzh1utPZqpeSL6838J4cbxby4Umoe5Uppo5h34mnmVxylM3XvhwijA5YLbG7PFex4moGF9YLTv6pa+4rdCPRvK4/viPFbWx9q6YrwHAIhhBBCCWRPu33hNT9//vzy71da+yInthacVCZA404k8hMlVR45xfZq3vTSqCiUTg5LVxQeqOntvEfZ6FDd+yDCdgI+2pzICyFE/vqmv+OCvRCz1hEp3iPGDz4YashBEUIIIRRb9rSbxGvWTw8Oj9PQ2efPOy9fecpfL+bzxTVPtrmCyE+sS58G88gpbh4xOhuZR5kip5dGRWFutpCfWI93lE7VvQ8ibCdg56PZghBidDa8F5LWWpciHzF+8MFQow6BEEIIoTiyp91+9ZqHjybyolCyt22XOVdwPURpVOSLa8E8paDpkfI0HU8rZW0iLwpzB5ITaqX4TcZcQQhh/xrMrzhKZ+reDxHGCDjQNevFfPMPg0cT+fzEelgvOP0bqLWyWJ0R9B8xdvD+HaU/aYIHRQghhFBsJfaaJ/XT/YOjNHR29vnFXy8D5ZcKzj3XwuzR/sHR2oR0JzZ/fS2YZ+36pWa6vO3myV9bcz6SC3F/lVPy19eaO47OHBztHxzNFMSliQ2n5LynBOVRzlP3foowTsCtrsnn7WZ3U5ohKY7u719/rYPFKvq31QKBIyYOvrnj7Kj2oAghhBCKLXvaTeA1j0/qn/YP09DZ2efnL3bSKz+x1iYuXZp46Eks/UMUZrSfdr3uXYyw3zsrveDTbEaEEELowsuedpN4zeP63qeDNHR2dvbn87/SKz+ZZgpCCPGPWV/KpWtrzV8fTly6NPGgh3XvboR93VmpBp9mMyKEEEIXXva0m8BrHh2f1Pb209Dp2dmzP1+kV34/K3N1z1zAFyZ4hBBCKFuyp90EXvMRAAAAAEASEnjN0zSp1+uplt/PZK7umQtYJtPBAwAAZIt6vZ7Aa1ZTRgjxxf4EAAAAuHjEMZoerxlzhzaoVqv3yo0vU0KInseQSJnurEwHjxBCCGVIfec1M2e5OtgTPY/hYgd8YYJHCCGEMqS+85o9b5FeKXMmO9OdlengEUIIoQyp77xm5ixXB3ui5zFc7IAvTPAIIYRQhtRBr7lgCGPh3F5TF+jU1SH3HzQO3+5E5RcnBwYnp5LmWZwcEMITSZxyYkhrsoNH7M7giKpXSGeNj3Q92oQK9ZoLw8IYd36VB15YjTo0DCLjkcdDMxjvCNEOS2U21Znldl+LkQVPSLeNgatlf/Wl7ANXy7rD2bs7aUNji94SWgcqjw0KoWtSu3ZJ27xXp5Jf5bHB1tGnrg6JVv8uDCu7z/1V19fKpg7pgp60hnJMJj9r+uCURAglU0e8Znmyee53wGsKleWaujokzTrO5bjtasefqMIvju1NePqeiI6hm1fPtr3mbcPpLK9J6ieF/1XjjK7y2KDPZjkpHZo1w3aU028bThgLw64/W5wcsJtXWUK8CEPPLGX3lccGnUleaYZ0Ke6vt42WtVqcHJCrMzjkGeoiBa/Zyu8xfB3rtXiaujrkmPXy2ODQwGBUn8peM7KvwzP3sDU6cNZ05ZRECHVamVjXLI8NSusfndL5vaY9GXdhXdN3xO4MjnOsa6bbcZ2QPvhWC4+PCP/qXTNdVamOT2x+K5DEVioTFdnCO0gz2G4bYmRhfCSwSBZxOLu0wBFdG704OTA4Oea27W1jYMTQNmkHvOb5uuyc3S3/PTY4Oe5aT29rtOk13Y5L4DW70hrntoM9PiURQu2qHa95enp2dFwPaH5UXCkp0hNIva4ZsvDTXGBxL6zG8KB9C29hbFC6l+ekC/kSrLgtJa8uqBZvfMF4y5HvPLp3wcZi301OsK7ZqpEx7gs+WFl1YJrdw+seM2BPB2VtXTM42XtuNQ6NLTamrg4NX1V96m0uf7NL9/4886U0iqTR4iwfOmU63q48NhiYbtv2mhGTcZI/bGKuawaHhG8lz7k7Pz4yNHY79DEAz4kgvGul3hRtkJIn819J/Geutze9vaO9EBljyh6XD22Pt8XJgZGFe/J6Z7fXNbvRGuOL0WdNrDHW7imJEOqV2vGa9dOzw6OTgOYK4kpJkZ5AagegvmK2bvpMXR1qXq9dv9UyXvb01jRSrZwtjygttLRSHGvYWn6QgvFe5rSz7OLkgPzgVNRVT8R6XtNbwWDwwcqqA9PsHl73wNAJqY7tqzL3vOaUtMLkPHTYtCzuR9LEFq+5mm0rz+Xe5y+Vo8U7Zrx3XaXB4B8hHosQ+rxm5JJ8Qq8ZDCBOVL5FOLetFMuWgdaWGs0Z7YHLgqIj5AKHxhaVVxL9mevvHf2FSNfjrUO7D2wsDAevRcKLwmaFNnVIem9aI95FpvUsr/BfQDp4SiKEuqu+85oKyxX5p3xwnVLe8OV0p1hpjpfwrqYkWhxSLLroy1H1hPqjmEdUBh+citQLuvHqHjPgls73EFjK416Z7vea8jJny+h7h1BIN3mMQrm11h7szZDx7AvMlTuVxlnripniUQfXNZ2REHRd8ihttpIx7q726W7yhp/acesbeAYmpFtDTiLt7poedzp0+LZrLu0NqcF1dY/ua+mkC+/fbrdGaIFxdP5TEiHUI7XlNeunB4fHAc0VxOisIj2BEjyv2bbXlF3moOamXjte0/vSRvKrnsJkR8YQK3h9YEnrHhg6kcNL4ZD6Q3G8phhZsF+XEUKI8McwFM0VaHbno/ER7wpT214z/LG8WM6yrec1lYo8XLM9I57XnLJrOjgU9i52zEaLDClBIaEnUWjHKXq8VXFj2MnZXJlzF2Lb95r6hz7D9+pCa8Tpo9B1zfOdkgihnqnvvKbScqnellXdrNF5Temei+oeuvwVIfbrC1LJCbymM524N/iSXPXaWdcMBh+sbFhgCeseL2Dp3mX21jXVb3xrOyWkuVTNHnx0oRxvSnae1/TcFz7/umY776GHN0hIivSEovY9dO+fQP4SAq2tGO3x76G7OfVXknKcszv0QqTscam1Pb0pr4Cex2u2825Q+q0R66wJ1zlPSYRQz9SO1zypn+4fHAVUKojRGUV6AiX7fs1F5z6O0mKW5Tl7SJGzdRF0/lAOPgAnfwlL2flIk+L51rfkXrOddc1g8MHKhgeWqO6BoaP7yH11oD8XNUOD997H1D1y6vET6ubyN3uMd4MCM7q7Hua8h+55LUP3WJ7q6y2dB+/82cK+ubazXtP7IJ1v1MUqIdDai76nFHx55EGueK6xVVndlaSsP7vl3tHvru5xqUGkdK+RCvGayr5WNrWmx/217lZrxDlroofZOU5JhFCv1I7XPD6pf9o/TEOp/N+gjNxJ6cx/suliZTP9r3fifb9mw/N1klrTkL5aq60IfeHqj1MSIZREbXnN4/rep4M0lIp9yYjX7IzJxmt2IPh++wr6fosHIYQQSqB2vObR8Ultbz8N8f/QM6TMBXxhgkcIIYQypHa85qPU+JIdQOZMdqY7K9PBI4QQQhlSe/83KC1Y18yQMhfwhQkeIYQQypDa8ZppI4T4Yn8CAAAAXDASe82YO7RBqoX3OZmre+YClsl08AAAABki/pz7/ygS4vdGZxv5AAAAAElFTkSuQmCC" alt="" />
sq_executesql的另一个与其接口有关的强大功能是,你可以使用输出参数为调用批处理中的变量返回值。利用该功能可以避免用临时表返回数据,从而得到更高效的代码和更少的重新编译。定义和使用输出参数的语法与存储过程类似。也就是说,你需要在声明参数时指定OUTPUT子句。例如,下面的静态代码简单的演示了如何从动态批处理中利用输出参数@p把值返回到外部批处理中的变量@i.
Declare @SQL nvarchar(max);
Declare @count int = ;
set @SQL = 'SELECT @n = MAX(DonatorId) FROM dbo.Donators';
EXEC sp_executesql
@stmt = @SQL,
@params = N'@n as int output',
@n = @count output; SELECT @count;

以上就是EXEC和sp_executesql的主要区别,如果各位看官觉得哪不对或者表达不清楚的,还请多多指出^_^SQLServer : EXEC和sp_executesql的区别-LMLPHP

05-11 09:38
查看更多