签名函数代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】签名函数
作者:Dezai.CN / 发布于2011/11/22/ 534
<div>
<p class="MsoNormal" align="left"><span lang="EN-US">--------------------------------------------------------------------</span>
<p class="MsoNormal" align="left"><span lang="EN-US">--</span><span>功能：签名函数</span>
<p class="MsoNormal" align="left"><span lang="EN-US">--</span><span>作者：</span>
<p class="MsoNormal" align="left"><span lang="EN-US">--</span><span>时间：年月日</span>
<p class="MsoNormal" align="left"><span lang="EN-US">--</span><span>使用：</span>
<p class="MsoNormal" align="left"><span lang="EN-US">--SELECT dbo.fn_SQLSigTSQL</span>
<p class="MsoNormal" align="left"><span lang="EN-US">--<span> </span>(N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78', 4000);</span>
<p class="MsoNormal" align="left"><span lang="EN-US">-------------------------------------------------------------------<span> </span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US"><span>IF</span><span style="color: rgb(0,0,0)"> </span><span>OBJECT_ID</span><span>(</span><span>'dbo.fn_SQLSigTSQL'</span><span>)</span><span style="color: rgb(0,0,0)"> </span><span>IS</span><span style="color: rgb(0,0,0)"> </span><span>NOT</span><span style="color: rgb(0,0,0)"> </span><span>NULL</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>DROP</span><span style="color: rgb(0,0,0)"> </span><span>FUNCTION</span><span style="color: rgb(0,0,0)"> dbo</span><span>.</span><span style="color: rgb(0,0,0)">fn_SQLSigTSQL</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span style="color: rgb(0,0,0)">GO</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">CREATE</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> </span><span>FUNCTION</span><span style="color: rgb(0,0,0)"> dbo</span><span>.</span><span style="color: rgb(0,0,0)">fn_SQLSigTSQL</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>(</span><span style="color: rgb(0,0,0)">@p1 </span><span>NTEXT</span><span>,</span><span style="color: rgb(0,0,0)"> @parselength </span><span>INT</span><span style="color: rgb(0,0,0)"> </span><span>=</span><span style="color: rgb(0,0,0)"> 4000</span><span>)</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">RETURNS</span><span lang="EN-US"><span style="color: rgb(0,0,0)"> </span><span>NVARCHAR</span><span>(</span><span style="color: rgb(0,0,0)">4000</span><span>)</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US">AS</span>
<p class="MsoNormal" align="left"><span lang="EN-US">BEGIN</span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>DECLARE</span><span style="color: rgb(0,0,0)"> @pos </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>INT</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>DECLARE</span><span style="color: rgb(0,0,0)"> @mode </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>CHAR</span><span>(</span><span style="color: rgb(0,0,0)">10</span><span>);</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>DECLARE</span><span style="color: rgb(0,0,0)"> @maxlength </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>INT</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>DECLARE</span><span style="color: rgb(0,0,0)"> @p2 </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>NCHAR</span><span>(</span><span style="color: rgb(0,0,0)">4000</span><span>);</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>DECLARE</span><span style="color: rgb(0,0,0)"> @currchar </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>CHAR</span><span>(</span><span style="color: rgb(0,0,0)">1</span><span>),</span><span style="color: rgb(0,0,0)"> @nextchar </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>CHAR</span><span>(</span><span style="color: rgb(0,0,0)">1</span><span>);</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>DECLARE</span><span style="color: rgb(0,0,0)"> @p2len </span><span>AS</span><span style="color: rgb(0,0,0)"> </span><span>INT</span><span>;</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @maxlength </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>LEN</span><span>(</span><span>RTRIM</span><span>(</span><span>SUBSTRING</span><span>(</span><span style="color: rgb(0,0,0)">@p1</span><span>,</span><span style="color: rgb(0,0,0)">1</span><span>,</span><span style="color: rgb(0,0,0)">4000</span><span>)));</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @maxlength </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>CASE</span><span style="color: rgb(0,0,0)"> </span><span>WHEN</span><span style="color: rgb(0,0,0)"> @maxlength </span><span>></span><span style="color: rgb(0,0,0)"> @parselength</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>THEN</span><span style="color: rgb(0,0,0)"> @parselength </span><span>ELSE</span><span style="color: rgb(0,0,0)"> @maxlength </span><span>END</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @pos </span><span>=</span><span style="color: rgb(0,0,0)"> 1</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @p2 </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>''</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @p2len </span><span>=</span><span style="color: rgb(0,0,0)"> 0</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @currchar </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>''</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>set</span><span style="color: rgb(0,0,0)"> @nextchar </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>''</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @mode </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>'command'</span><span>;</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US"><span>WHILE</span><span style="color: rgb(0,0,0)"> </span><span>(</span><span style="color: rgb(0,0,0)">@pos </span><span><=</span><span style="color: rgb(0,0,0)"> @maxlength</span><span>)</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>BEGIN</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @currchar </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>SUBSTRING</span><span>(</span><span style="color: rgb(0,0,0)">@p1</span><span>,</span><span style="color: rgb(0,0,0)">@pos</span><span>,</span><span style="color: rgb(0,0,0)">1</span><span>);</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @nextchar </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>SUBSTRING</span><span>(</span><span style="color: rgb(0,0,0)">@p1</span><span>,</span><span style="color: rgb(0,0,0)">@pos</span><span>+</span><span style="color: rgb(0,0,0)">1</span><span>,</span><span style="color: rgb(0,0,0)">1</span><span>);</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>IF</span><span style="color: rgb(0,0,0)"> @mode </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>'command'</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>BEGIN</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @p2 </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>LEFT(</span><span style="color: rgb(0,0,0)">@p2</span><span>,</span><span style="color: rgb(0,0,0)">@p2len</span><span>)</span><span style="color: rgb(0,0,0)"> </span><span>+</span><span style="color: rgb(0,0,0)"> @currchar</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @p2len </span><span>=</span><span style="color: rgb(0,0,0)"> @p2len </span><span>+</span><span style="color: rgb(0,0,0)"> 1 </span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>IF</span><span style="color: rgb(0,0,0)"> @currchar </span><span>IN</span><span style="color: rgb(0,0,0)"> </span><span>(</span><span>','</span><span>,</span><span>'('</span><span>,</span><span>' '</span><span>,</span><span>'='</span><span>,</span><span>'<'</span><span>,</span><span>'>'</span><span>,</span><span>'!'</span><span>)</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>AND</span><span style="color: rgb(0,0,0)"> @nextchar </span><span>BETWEEN</span><span style="color: rgb(0,0,0)"> </span><span>'0'</span><span style="color: rgb(0,0,0)"> </span><span>AND</span><span style="color: rgb(0,0,0)"> </span><span>'9'</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>BEGIN</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @mode </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>'number'</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @p2 </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>LEFT(</span><span style="color: rgb(0,0,0)">@p2</span><span>,</span><span style="color: rgb(0,0,0)">@p2len</span><span>)</span><span style="color: rgb(0,0,0)"> </span><span>+</span><span style="color: rgb(0,0,0)"> </span><span>'#'</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @p2len </span><span>=</span><span style="color: rgb(0,0,0)"> @p2len </span><span>+</span><span style="color: rgb(0,0,0)"> 1</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>END</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>IF</span><span style="color: rgb(0,0,0)"> @currchar </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>''''</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>BEGIN</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @mode </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>'literal'</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @p2 </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>LEFT(</span><span style="color: rgb(0,0,0)">@p2</span><span>,</span><span style="color: rgb(0,0,0)">@p2len</span><span>)</span><span style="color: rgb(0,0,0)"> </span><span>+</span><span style="color: rgb(0,0,0)"> </span><span>'#'''</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @p2len </span><span>=</span><span style="color: rgb(0,0,0)"> @p2len </span><span>+</span><span style="color: rgb(0,0,0)"> 2</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>END</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>END</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>ELSE</span><span style="color: rgb(0,0,0)"> </span><span>IF</span><span style="color: rgb(0,0,0)"> @mode </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>'number'</span><span style="color: rgb(0,0,0)"> </span><span>AND</span><span style="color: rgb(0,0,0)"> @nextchar </span><span>IN</span><span style="color: rgb(0,0,0)"> </span><span>(</span><span>','</span><span>,</span><span>')'</span><span>,</span><span>' '</span><span>,</span><span>'='</span><span>,</span><span>'<'</span><span>,</span><span>'>'</span><span>,</span><span>'!'</span><span>)</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @mode</span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>'command'</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>ELSE</span><span style="color: rgb(0,0,0)"> </span><span>IF</span><span style="color: rgb(0,0,0)"> @mode </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>'literal'</span><span style="color: rgb(0,0,0)"> </span><span>AND</span><span style="color: rgb(0,0,0)"> @currchar </span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>''''</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @mode</span><span>=</span><span style="color: rgb(0,0,0)"> </span><span>'command'</span><span>;</span></span>
<p class="MsoNormal" align="left">
<p class="MsoNormal" align="left"><span lang="EN-US"><span>SET</span><span style="color: rgb(0,0,0)"> @pos </span><span>=</span><span style="color: rgb(0,0,0)"> @pos </span><span>+</span><span style="color: rgb(0,0,0)"> 1</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>END</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span>RETURN</span><span style="color: rgb(0,0,0)"> @p2</span><span>;</span></span>
<p class="MsoNormal" align="left"><span lang="EN-US">END</span>
<p class="MsoNormal" align="left"><span lang="EN-US"><span style="color: rgb(0,0,0)">GO</span></span>
</div>
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
　签名函数　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)