SQLServer2008 动态SQL实践

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不是程序代码在测试的时候会不方便一些，但是它会使程序的执行效率大大提高还是从这一点上说还是值得的。