SQL Server的动态SQL功能听说了很长时间了，但是一直没有实践过。通常的项目中都是在程序中拼写SQL然后送到SQL Server中去执行，不过这样对于复杂一些或者数据量大的SQL来说不是最优，使用存储过程就是一种很好的选择方案。 一个最简单的动态SQL exec sp_exe
SQL Server的动态SQL功能听说了很长时间了，但是一直没有实践过。通常的项目中都是在程序中拼写SQL然后送到SQL Server中去执行，不过这样对于复杂一些或者数据量大的SQL来说不是最优，使用存储过程就是一种很好的选择方案。
一个最简单的动态SQL
<span>exec</span> sp_executesql N<span>'</span><span>select * from emp</span><span>'</span>
当然我们使用动态SQL不是来做这样简单的事情。
看看下面这个，通常我们存储过程都是这样的。
<span> 1</span> <span>CREATE</span> <span>PROCEDURE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>mytest</span><span>]</span><br><span> 2</span> <span>@id</span> <span>nchar</span>(<span>5</span>),<br><span> 3</span> <span>@s_date</span> <span>nchar</span>(<span>10</span>),<br><span> 4</span> <span>@e_date</span> <span>nchar</span>(<span>10</span>)<br><span> 5</span> <span>AS</span><br><span> 6</span> <br><span> 7</span> <span>declare</span> <span>@sql</span> <span>varchar</span>(<span>4000</span>)<br><span> 8</span> <br><span> 9</span> <span>begin</span><br><span>10</span> <span>select</span> <span>*</span> <span>from</span> emp <br><span>11</span> <span>where</span> work_date <span>>=</span> <span>'</span><span> + @s_date + </span><span>'</span> <span>and</span> work_date <span> <span>'</span><span> + @e_date + </span><span>'</span><br><span>12</span> <span>end</span></span>
但是如果因为业务需要传进来的参数可能为空，这个时候就需要进行判断，但是上面的代码无法完成这种需求。我们这里只是一种假设，实际的情况可能比这个复杂一些。这时候我们就需要动态SQL了。
下面这个存储过程通过使用动态SQL就很容易实现了我们程序上的这个需要。
<span>CREATE</span> <span>PROCEDURE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>mytest</span><span>]</span><br> <span>@id</span> <span>nchar</span>(<span>5</span>),<br> <span>@s_date</span> <span>nchar</span>(<span>10</span>),<br> <span>@e_date</span> <span>nchar</span>(<span>10</span>)<br><span>AS</span><br><br><span>declare</span> <span>@sql</span> <span>varchar</span>(<span>4000</span>)<br><br><span>begin</span><br><span>set</span> <span>@sql</span><span>=</span><span>'</span><span>select * from emp </span><span>'</span><br><br> <span>if</span> (<span>@s_date</span> <span></span> <span>''</span>) <span>and</span> (<span>@e_date</span> <span></span> <span>''</span>)<br> <span>set</span> <span>@sql</span> <span>=</span> <span>@sql</span> <span>+</span> <span>'</span><span> where work_date >= </span><span>'''</span> <span>+</span> <span>@s_date</span> <span>+</span> <span>'''</span><span> and work_date <span>'''</span> <span>+</span> <span>@e_date</span> <span>+</span> <span>''''</span><br> <span>else</span><br> <span>set</span> <span>@sql</span> <span>=</span> <span>@sql</span> <span>+</span> <span>'</span><span> where work_date is null</span><span>'</span><br><span>end</span></span>
这里要注意一个问题，还是先看例子
<span> 1</span> <span>CREATE</span> <span>PROCEDURE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>mytest</span><span>]</span><br><span> 2</span> <span>@id</span> <span>nchar</span>(<span>5</span>),<br><span> 3</span> <span>@s_date</span> <span>nchar</span>(<span>10</span>),<br><span> 4</span> <span>@e_date</span> <span>nchar</span>(<span>10</span>)<br><span> 5</span> <span>AS</span><br><span> 6</span> <br><span> 7</span> <span>declare</span> <span>@sql</span> <span>varchar</span>(<span>4000</span>)<br><span> 8</span> <br><span> 9</span> <span>begin</span><br><span>10</span> <span>set</span> <span>@sql</span><span>=</span><span>'</span><span>select * from emp <br></span><span>11</span> <span> where id=</span><span>''</span><span>1</span><span>''</span><span> and work_date is null</span><span>'</span><br><span>12</span> <span>end</span>
注意第11行
set @sql='select * from emp
11 where id=''1'' and work_date= ''' + @s_date + ''''
如果写成
set @sql='select * from emp
11 where id='1' and work_date= ' + @s_date + '
就是错误的，这个想必大家都明白原因，只是写的时候往往会忽略这个问题，这里提醒一下大家。
另一个需要注意的是字符型的变量的判断，要使用''来判断是否为空而不能使用 is not null
if (@s_date '') and (@e_date '')
set @sql = @sql + ' where work_date >= ''' + @s_date + ''' and work_date else
set @sql = @sql + ' where work_date is null'
最后一个例子，在游标中使用动态SQL，因为在游标中不能直接使用动态SQL，所以需要借助临时表来，完成动态SQL在游标中的循环执行。
<span> 1</span> <span>BEGIN</span> <span>TRANSACTION</span><br><span> 2</span> <br><span> 3</span> <span>--</span><span>定义临时表</span><span><br></span><span> 4</span> <span>create</span> <span>table</span> #tmp_table <br><span> 5</span> (<br><span> 6</span> id <span>nchar</span>(<span>5</span>),<br><span> 7</span> ...<br><span> 8</span> <br><span> 9</span> )<br><span>10</span> <br><span>11</span> <span>--</span><span>执行动态SQL将记录插入到临时表中</span><span><br></span><span>12</span> <span>insert</span> <span>into</span> #tmp_table (id,...) <span>EXECUTE</span> sp_executesql <span>@sql</span> <br><span>13</span> <br><span>14</span> <span>--</span><span>在游标中便利游标</span><span><br></span><span>15</span> <span>Declare</span> cur_tmp <span>Cursor</span> Scroll<br><span>16</span> <span>For</span> <br><span>17</span> <span>select</span> (id,...) <span>from</span> #tmp_table<br><span>18</span> <span>OPEN</span> cur_tmp <br><span>19</span> <br><span>20</span> <span>Fetch</span> <span>next</span> <span>from</span> cur_tmp <br><span>21</span> <br><span>22</span> <span>into</span> <span>@id</span>,...<br><span>23</span> <br><span>24</span> <span>while</span> <span>@@fetch_status</span><span>=</span><span>0</span><br><span>25</span> <span>begin</span><br><span>26</span> <br><span>27</span> <br><span>28</span> ...<br><span>29</span> <span>fetch</span> <span>next</span> <span>from</span> cur_tmp<br><span>30</span> <span>into</span> <span>@id</span>,...<br><span>31</span> <br><span>32</span> <br><span>33</span> <span>end</span><br><span>34</span> <span>CLOSE</span> cur_tmp <br><span>35</span> <span>drop</span> <span>table</span> #tmp_table<br><span>36</span> <br><span>37</span> <span>Deallocate</span> cur_tmp<br><span>38</span> <br><span>39</span> <br><span>40</span> <br><span>41</span> <span>if</span> <span>@@error</span> <span></span> <span>0</span><br><span>42</span> <span>begin</span><br><span>43</span> <br><span>44</span> <span>ROLLBACK</span> <span>TRANSACTION</span><br><span>45</span> <br><span>46</span> <span>if</span> <span>not</span> (<span>select</span> <span>object_id</span>(<span>'</span><span>Tempdb..#tmp_table</span><span>'</span>)) <span>is</span> <span>null</span> <br><span>47</span> <span>drop</span> <span>table</span> #tmp_table<br><span>48</span> <br><span>49</span> <span>COMMIT</span> <span>TRANSACTION</span>
动态SQL使储存过程的实现更加的灵活和方便，但是由于SQL不是程序代码在测试的时候会不方便一些，但是它会使程序的执行效率大大提高还是从这一点上说还是值得的。
mysql：blob和其他無-SQL存儲，有什麼區別？May 13, 2025 am 12:14 AMmysql'sblobissuitableForStoringBinaryDataWithInareLationalDatabase，而ilenosqloptionslikemongodb，redis和calablesolutionsolutionsolutionsoluntionsoluntionsolundortionsolunsonstructureddata.blobobobissimplobisslowdeperformberbutslowderformandperformancewithlararengedata;
mySQL添加用戶：語法，選項和安全性最佳實踐May 13, 2025 am 12:12 AMtoaddauserinmysql，使用：createUser'username'@'host'Indessify'password'; there'showtodoitsecurely：1）choosethehostcarecarefullytocon trolaccess.2）setResourcelimitswithoptionslikemax_queries_per_hour.3）usestrong，iniquepasswords.4）Enforcessl/tlsconnectionswith
MySQL：如何避免字符串數據類型常見錯誤？May 13, 2025 am 12:09 AMtoAvoidCommonMistakeswithStringDatatatPesInMysQl，CloseStringTypenuances，chosethirtightType，andManageEngencodingAndCollationsEttingSefectery.1）usecharforfixed lengengtrings，varchar forvariable-varchar forbariaible length，andtext/blobforlargerdataa.2 seterters seterters seterters
mySQL：字符串數據類型和枚舉？May 13, 2025 am 12:05 AMmysqloffersechar，varchar，text，and denumforstringdata.usecharforfixed Lengttrings，varcharerforvariable長度，文本forlarger文本，andenumforenforcingDataAntegrityWithaEtofValues。
mysql blob：如何優化斑點請求May 13, 2025 am 12:03 AM優化MySQLBLOB請求可以通過以下策略：1.減少BLOB查詢頻率，使用獨立請求或延遲加載；2.選擇合適的BLOB類型（如TINYBLOB）；3.將BLOB數據分離到單獨表中；4.在應用層壓縮BLOB數據；5.對BLOB元數據建立索引。這些方法結合實際應用中的監控、緩存和數據分片，可以有效提升性能。
將用戶添加到MySQL：完整的教程May 12, 2025 am 12:14 AM掌握添加MySQL用戶的方法對於數據庫管理員和開發者至關重要，因為它確保數據庫的安全性和訪問控制。 1)使用CREATEUSER命令創建新用戶，2)通過GRANT命令分配權限，3)使用FLUSHPRIVILEGES確保權限生效，4)定期審計和清理用戶賬戶以維護性能和安全。
掌握mySQL字符串數據類型：varchar vs.文本與charMay 12, 2025 am 12:12 AMchosecharforfixed-lengthdata，varcharforvariable-lengthdata，andtextforlargetextfield.1）chariseffity forconsistent-lengthdatalikecodes.2）varcharsuitsvariable-lengthdatalikenames，ballancingflexibilitibility andperformance.3）
MySQL：字符串數據類型和索引：最佳實踐May 12, 2025 am 12:11 AM在MySQL中處理字符串數據類型和索引的最佳實踐包括：1)選擇合適的字符串類型，如CHAR用於固定長度，VARCHAR用於可變長度，TEXT用於大文本；2)謹慎索引，避免過度索引，針對常用查詢創建索引；3)使用前綴索引和全文索引優化長字符串搜索；4)定期監控和優化索引，保持索引小巧高效。通過這些方法，可以在讀取和寫入性能之間取得平衡，提升數據庫效率。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
熱工具
EditPlus 中文破解版
體積小，語法高亮，不支援程式碼提示功能
PhpStorm Mac 版本
最新（2018.2.1 ）專業的PHP整合開發工具
SublimeText3 Linux新版
SublimeText3 Linux最新版
WebStorm Mac版
好用的JavaScript開發工具
ZendStudio 13.5.1 Mac
強大的PHP整合開發環境
