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不是程序代码在测试的时候会不方便一些，但是它会使程序的执行效率大大提高还是从这一点上说还是值得的。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1662
14
1418
52
1311
25
1261
29
1234
24
What is the difference between HQL and SQL in Hibernate framework?
Apr 17, 2024 pm 02:57 PM
HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).
Convert VirtualBox fixed disk to dynamic disk and vice versa
Mar 25, 2024 am 09:36 AM
When creating a virtual machine, you will be asked to select a disk type, you can select fixed disk or dynamic disk. What if you choose fixed disks and later realize you need dynamic disks, or vice versa? Good! You can convert one to the other. In this post, we will see how to convert VirtualBox fixed disk to dynamic disk and vice versa. A dynamic disk is a virtual hard disk that initially has a small size and grows in size as you store data in the virtual machine. Dynamic disks are very efficient at saving storage space because they only take up as much host storage space as needed. However, as disk capacity expands, your computer's performance may be slightly affected. Fixed disks and dynamic disks are commonly used in virtual machines
Comparison and differences of SQL syntax between Oracle and DB2
Mar 11, 2024 pm 12:09 PM
Oracle and DB2 are two commonly used relational database management systems, each of which has its own unique SQL syntax and characteristics. This article will compare and differ between the SQL syntax of Oracle and DB2, and provide specific code examples. Database connection In Oracle, use the following statement to connect to the database: CONNECTusername/password@database. In DB2, the statement to connect to the database is as follows: CONNECTTOdataba
Usage of division operation in Oracle SQL
Mar 10, 2024 pm 03:06 PM
"Usage of Division Operation in OracleSQL" In OracleSQL, division operation is one of the common mathematical operations. During data query and processing, division operations can help us calculate the ratio between fields or derive the logical relationship between specific values. This article will introduce the usage of division operation in OracleSQL and provide specific code examples. 1. Two ways of division operations in OracleSQL In OracleSQL, division operations can be performed in two different ways.
Detailed explanation of the Set tag function in MyBatis dynamic SQL tags
Feb 26, 2024 pm 07:48 PM
Interpretation of MyBatis dynamic SQL tags: Detailed explanation of Set tag usage MyBatis is an excellent persistence layer framework. It provides a wealth of dynamic SQL tags and can flexibly construct database operation statements. Among them, the Set tag is used to generate the SET clause in the UPDATE statement, which is very commonly used in update operations. This article will explain in detail the usage of the Set tag in MyBatis and demonstrate its functionality through specific code examples. What is Set tag Set tag is used in MyBati
How to solve the 5120 error in SQL
Mar 06, 2024 pm 04:33 PM
Solution: 1. Check whether the logged-in user has sufficient permissions to access or operate the database, and ensure that the user has the correct permissions; 2. Check whether the account of the SQL Server service has permission to access the specified file or folder, and ensure that the account Have sufficient permissions to read and write the file or folder; 3. Check whether the specified database file has been opened or locked by other processes, try to close or release the file, and rerun the query; 4. Try as administrator Run Management Studio as etc.
Database technology competition: What are the differences between Oracle and SQL?
Mar 09, 2024 am 08:30 AM
Database technology competition: What are the differences between Oracle and SQL? In the database field, Oracle and SQL Server are two highly respected relational database management systems. Although they both belong to the category of relational databases, there are many differences between them. In this article, we will delve into the differences between Oracle and SQL Server, as well as their features and advantages in practical applications. First of all, there are differences in syntax between Oracle and SQL Server.
PHP Coding Practices: Refusing Alternatives to Goto Statements
Mar 28, 2024 pm 09:24 PM
PHP Coding Practices: Refusal to Use Alternatives to Goto Statements In recent years, with the continuous updating and iteration of programming languages, programmers have begun to pay more attention to coding specifications and best practices. In PHP programming, the goto statement has existed as a control flow statement for a long time, but in practical applications it often leads to a decrease in the readability and maintainability of the code. This article will share some alternatives to help developers refuse to use goto statements and improve code quality. 1. Why refuse to use goto statement? First, let's think about why
