数据库多级分类代码代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】数据库多级分类代码
作者:Dezai.CN / 发布于2012/2/16/ 490
<div>
<div class="container">
<div class="line number1 index0 alt2">"sql comments">--****************************** </div>
<div class="line number2 index1 alt1">"sql comments">-- 多级分类存储过程  </div>
<div class="line number3 index2 alt2">"sql comments">-- WDFrog  2012-2-15 </div>
<div class="line number4 index3 alt1">"sql comments">-- <a href="http://wdfrog.cnblogs.com/">http://wdfrog.cnblogs.com</a> </div>
<div class="line number5 index4 alt2">"sql comments">--****************************** </div>
<div class="line number6 index5 alt1">"sql spaces"> </div>
<div class="line number7 index6 alt2">"sql comments">--****************************** </div>
<div class="line number8 index7 alt1">"sql comments">--数据表定义             </div>
<div class="line number9 index8 alt2">"sql comments">--****************************** </div>
<div class="line number10 index9 alt1">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category]'"sql plain">)  "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsUserTable'"sql plain">) = 1)  </div>
<div class="line number11 index10 alt2">"sql keyword">drop  "sql keyword">table "sql plain">[dbo].[Category]  </div>
<div class="line number12 index11 alt1">"sql plain">GO </div>
<div class="line number13 index12 alt2">"sql spaces"> </div>
<div class="line number14 index13 alt1">"sql keyword">CREATE  "sql keyword">TABLE "sql plain">[dbo].[Category]  ( </div>
<div class="line number15 index14 alt2">"sql spaces">    "sql plain">[ClassID] ["sql keyword">int"sql plain">] "sql color1">NOT "sql color1">NULL "sql plain">, </div>
<div class="line number16 index15 alt1">"sql spaces">    "sql plain">[ClassName] [nvarchar]  (50) "sql keyword">COLLATE "sql plain">Chinese_PRC_CI_AS "sql color1">NOT  "sql color1">NULL "sql plain">, </div>
<div class="line number17 index16 alt2">"sql spaces">    "sql plain">[Code] [nvarchar] (200)  "sql keyword">COLLATE "sql plain">Chinese_PRC_CI_AS "sql color1">NOT  "sql color1">NULL "sql plain">, </div>
<div class="line number18 index17 alt1">"sql spaces">    "sql plain">[DataNum] ["sql keyword">int"sql plain">] "sql color1">NULL "sql plain">, </div>
<div class="line number19 index18 alt2">"sql spaces">     </div>
<div class="line number20 index19 alt1">"sql spaces">    "sql plain">[Info] [nvarchar] (1000)  "sql keyword">COLLATE "sql plain">Chinese_PRC_CI_AS "sql color1">NULL</div>
<div class="line number21 index20 alt2">"sql spaces">     </div>
<div class="line number22 index21 alt1">"sql plain">) "sql keyword">ON "sql plain">["sql keyword">PRIMARY"sql plain">] </div>
<div class="line number23 index22 alt2">"sql plain">GO </div>
<div class="line number24 index23 alt1">"sql spaces"> </div>
<div class="line number25 index24 alt2">"sql keyword">ALTER  "sql keyword">TABLE "sql plain">[dbo].[Category]  "sql keyword">ADD</div>
<div class="line number26 index25 alt1">"sql spaces">    "sql keyword">CONSTRAINT "sql plain">[DF_Category_DataNum] "sql keyword">DEFAULT "sql plain">(0) "sql keyword">FOR "sql plain">[DataNum], </div>
<div class="line number27 index26 alt2">"sql spaces">    "sql keyword">CONSTRAINT "sql plain">[PK_Category] "sql keyword">PRIMARY  "sql keyword">KEY  "sql plain">CLUSTERED   </div>
<div class="line number28 index27 alt1">"sql spaces">    "sql plain">( </div>
<div class="line number29 index28 alt2">"sql spaces">        "sql plain">[ClassID]  </div>
<div class="line number30 index29 alt1">"sql spaces">    "sql plain">)  "sql keyword">ON "sql plain">["sql keyword">PRIMARY"sql plain">]  </div>
<div class="line number31 index30 alt2">"sql plain">GO </div>
<div class="line number32 index31 alt1">"sql spaces"> </div>
<div class="line number33 index32 alt2">"sql spaces"> </div>
<div class="line number34 index33 alt1">"sql comments">--************************* </div>
<div class="line number35 index34 alt2">"sql comments">-- 添加分类存储过程  </div>
<div class="line number36 index35 alt1">"sql comments">--*************************** </div>
<div class="line number37 index36 alt2">"sql spaces"> </div>
<div class="line number38 index37 alt1">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category_Add]'"sql plain">)  "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsProcedure'"sql plain">) = 1)  </div>
<div class="line number39 index38 alt2">"sql keyword">drop  "sql keyword">procedure "sql plain">[dbo].[Category_Add] </div>
<div class="line number40 index39 alt1">"sql plain">GO </div>
<div class="line number41 index40 alt2">"sql spaces"> </div>
<div class="line number42 index41 alt1">"sql spaces"> </div>
<div class="line number43 index42 alt2">"sql keyword">Create   "sql plain">Proc Category_Add  </div>
<div class="line number44 index43 alt1">"sql plain">@ClassName  nvarchar(50), </div>
<div class="line number45 index44 alt2">"sql plain">@DataNum  "sql keyword">int "sql plain">,  </div>
<div class="line number46 index45 alt1">"sql spaces"> </div>
<div class="line number47 index46 alt2">"sql plain">@Info  nvarchar(1000), </div>
<div class="line number48 index47 alt1">"sql plain">@ParentID  "sql keyword">int "sql comments">-- 0表示根类别  </div>
<div class="line number49 index48 alt2">"sql keyword">As</div>
<div class="line number50 index49 alt1">"sql keyword">Declare  "sql plain">@EditCode "sql keyword">int</div>
<div class="line number51 index50 alt2">"sql keyword">Declare  "sql plain">@StepLen "sql keyword">int</div>
<div class="line number52 index51 alt1">"sql keyword">Declare  "sql plain">@matchStr nvarchar(50) </div>
<div class="line number53 index52 alt2">"sql keyword">Declare  "sql plain">@typeCode nvarchar(50) </div>
<div class="line number54 index53 alt1">"sql keyword">Declare  "sql plain">@Code nvarchar(200) </div>
<div class="line number55 index54 alt2">"sql keyword">Declare  "sql plain">@MyCode nvarchar(200) </div>
<div class="line number56 index55 alt1">"sql keyword">Declare  "sql plain">@ParentCode nvarchar(200)  </div>
<div class="line number57 index56 alt2">"sql keyword">Declare  "sql plain">@selfCode "sql keyword">int</div>
<div class="line number58 index57 alt1">"sql keyword">Set  "sql plain">@editCode=1 </div>
<div class="line number59 index58 alt2">"sql keyword">Set  "sql plain">@StepLen=4 </div>
<div class="line number60 index59 alt1">"sql keyword">Set  "sql plain">@matchStr=REPLICATE("sql string">'_'"sql plain">,@StepLen) "sql comments">--4个_ </div>
<div class="line number61 index60 alt2">"sql keyword">set  "sql plain">@typeCode="sql string">''</div>
<div class="line number62 index61 alt1">"sql keyword">Set  "sql plain">@Code="sql string">''</div>
<div class="line number63 index62 alt2">"sql keyword">Set  "sql plain">@MyCode="sql string">''</div>
<div class="line number64 index63 alt1">"sql keyword">Set  "sql plain">@selfCode=0 </div>
<div class="line number65 index64 alt2">"sql keyword">Set  "sql plain">@ParentCode="sql string">''</div>
<div class="line number66 index65 alt1">"sql spaces"> </div>
<div class="line number67 index66 alt2">"sql keyword">Select  "sql plain">@ParentCode=Code "sql keyword">From "sql plain">[Category] "sql keyword">Where "sql plain">ClassID=@ParentID  </div>
<div class="line number68 index67 alt1">"sql spaces"> </div>
<div class="line number69 index68 alt2">"sql plain">If(@editCode=1)  </div>
<div class="line number70 index69 alt1">"sql spaces">    "sql keyword">Begin</div>
<div class="line number71 index70 alt2">"sql spaces">        "sql comments">--获取子类中编号最大的Code,column.ParentCode + matchStr中  </div>
<div class="line number72 index71 alt1">"sql spaces">    "sql keyword">Select "sql keyword">Top "sql plain">1 @MyCode= Code  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">Code  "sql color1">Like "sql plain">@ParentCode  + @matchStr "sql keyword">Order "sql keyword">By "sql plain">Code "sql keyword">DESC</div>
<div class="line number73 index72 alt2">"sql spaces">    "sql plain">If @@ROWCOUNT >0  </div>
<div class="line number74 index73 alt1">"sql spaces">        "sql keyword">Begin</div>
<div class="line number75 index74 alt2">"sql spaces">        "sql keyword">Set "sql plain">@selfCode="sql color2">Cast"sql plain">("sql color2">Right"sql plain">(@MyCode,@StepLen) "sql keyword">As  "sql keyword">Int "sql plain">) +1 </div>
<div class="line number76 index75 alt1">"sql spaces">        "sql keyword">Set "sql plain">@typeCode=Replicate("sql string">'0'"sql plain">,@StepLen-1) + "sql color2">Cast"sql plain">(@selfCode "sql keyword">As "sql plain">nvarchar) </div>
<div class="line number77 index76 alt2">"sql spaces">                "sql keyword">Set  "sql plain">@typeCode="sql color2">Right"sql plain">(@typeCode,@StepLen)  </div>
<div class="line number78 index77 alt1">"sql spaces">                "sql keyword">Set  "sql plain">@typeCode=@ParentCode + @TypeCode </div>
<div class="line number79 index78 alt2">"sql spaces">        "sql keyword">End</div>
<div class="line number80 index79 alt1">"sql spaces">    "sql keyword">Else</div>
<div class="line number81 index80 alt2">"sql spaces">        "sql keyword">Begin</div>
<div class="line number82 index81 alt1">"sql spaces">        "sql keyword">Set "sql plain">@typeCode=@ParentCode +Replicate("sql string">'0'"sql plain">,@StepLen-1)+"sql string">'1'</div>
<div class="line number83 index82 alt2">"sql spaces">        "sql keyword">End</div>
<div class="line number84 index83 alt1">"sql spaces">    "sql keyword">End</div>
<div class="line number85 index84 alt2">"sql keyword">Declare  "sql plain">@ClassID "sql keyword">int</div>
<div class="line number86 index85 alt1">"sql keyword">Set  "sql plain">@ClassID=0 </div>
<div class="line number87 index86 alt2">"sql spaces">      "sql comments">--获取最大ClassID  </div>
<div class="line number88 index87 alt1">"sql spaces">      "sql keyword">Select "sql plain">@ClassId="sql keyword">Max"sql plain">(ClassID) "sql keyword">From "sql plain">[Category] </div>
<div class="line number89 index88 alt2">"sql spaces">      "sql plain">If "sql color1">Not "sql plain">@ClassID "sql keyword">Is "sql color1">Null</div>
<div class="line number90 index89 alt1">"sql spaces">         "sql keyword">Begin</div>
<div class="line number91 index90 alt2">"sql spaces">           "sql keyword">Set "sql plain">@ClassId=@ClassID +1 </div>
<div class="line number92 index91 alt1">"sql spaces">         "sql keyword">End</div>
<div class="line number93 index92 alt2">"sql spaces">      "sql keyword">Else</div>
<div class="line number94 index93 alt1">"sql spaces">         "sql keyword">Begin</div>
<div class="line number95 index94 alt2">"sql spaces">           "sql keyword">Set "sql plain">@ClassID=1 </div>
<div class="line number96 index95 alt1">"sql spaces">         "sql keyword">End</div>
<div class="line number97 index96 alt2">"sql spaces"> </div>
<div class="line number98 index97 alt1">"sql spaces">  </div>
<div class="line number99 index98 alt2">"sql spaces">      "sql keyword">Insert "sql keyword">into "sql plain">[Category] </div>
<div class="line number100 index99 alt1">"sql spaces">            "sql plain">(ClassID,ClassName,Code,DataNum, Info) </div>
<div class="line number101 index100 alt2">"sql spaces">      "sql keyword">values</div>
<div class="line number102 index101 alt1">"sql spaces">            "sql plain">(@ClassID,@ClassName,@typeCode,@DataNum, @Info) </div>
<div class="line number103 index102 alt2">"sql spaces">             </div>
<div class="line number104 index103 alt1">"sql spaces">      "sql keyword">Select "sql plain">@ClassID "sql keyword">As "sql plain">ClassID       </div>
<div class="line number105 index104 alt2">"sql plain">Go  </div>
<div class="line number106 index105 alt1">"sql spaces"> </div>
<div class="line number107 index106 alt2">"sql spaces"> </div>
<div class="line number108 index107 alt1">"sql comments">--******************** </div>
<div class="line number109 index108 alt2">"sql comments">-- 修改分类存储过程  </div>
<div class="line number110 index109 alt1">"sql comments">--********************* </div>
<div class="line number111 index110 alt2">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category_Update]'"sql plain">)  "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsProcedure'"sql plain">) = 1)  </div>
<div class="line number112 index111 alt1">"sql keyword">drop  "sql keyword">procedure "sql plain">[dbo].[Category_Update] </div>
<div class="line number113 index112 alt2">"sql plain">GO  </div>
<div class="line number114 index113 alt1">"sql spaces"> </div>
<div class="line number115 index114 alt2">"sql keyword">Create   "sql plain">Proc Category_Update  </div>
<div class="line number116 index115 alt1">"sql plain">@ClassID  "sql keyword">int "sql plain">,  "sql comments">--需要修改的ClassID </div>
<div class="line number117 index116 alt2">"sql plain">@ClassName  nvarchar(50), </div>
<div class="line number118 index117 alt1">"sql spaces"> </div>
<div class="line number119 index118 alt2">"sql plain">@Info  nvarchar(1000), </div>
<div class="line number120 index119 alt1">"sql plain">@ParentID  "sql keyword">int</div>
<div class="line number121 index120 alt2">"sql keyword">As</div>
<div class="line number122 index121 alt1">"sql keyword">Declare "sql plain">@EditCode  "sql keyword">int</div>
<div class="line number123 index122 alt2">"sql keyword">Declare "sql plain">@StepLen "sql keyword">int</div>
<div class="line number124 index123 alt1">"sql keyword">Declare "sql plain">@matchStr  nvarchar(50) </div>
<div class="line number125 index124 alt2">"sql keyword">Declare "sql plain">@typeCode  nvarchar(50) </div>
<div class="line number126 index125 alt1">"sql keyword">Declare "sql plain">@Code nvarchar(200)  </div>
<div class="line number127 index126 alt2">"sql keyword">Declare "sql plain">@MyCode nvarchar(200)  </div>
<div class="line number128 index127 alt1">"sql keyword">Declare "sql plain">@ParentCode  nvarchar(200) </div>
<div class="line number129 index128 alt2">"sql keyword">Declare "sql plain">@selfCode  "sql keyword">int</div>
<div class="line number130 index129 alt1">"sql keyword">Set  "sql plain">@editCode=0 </div>
<div class="line number131 index130 alt2">"sql keyword">Set  "sql plain">@StepLen=4 </div>
<div class="line number132 index131 alt1">"sql keyword">Set  "sql plain">@matchStr=REPLICATE("sql string">'_'"sql plain">,@StepLen) "sql comments">--4个_ </div>
<div class="line number133 index132 alt2">"sql keyword">set  "sql plain">@typeCode="sql string">''</div>
<div class="line number134 index133 alt1">"sql keyword">Set  "sql plain">@Code="sql string">''</div>
<div class="line number135 index134 alt2">"sql keyword">Set  "sql plain">@MyCode="sql string">''</div>
<div class="line number136 index135 alt1">"sql keyword">Set  "sql plain">@selfCode=0 </div>
<div class="line number137 index136 alt2">"sql keyword">Set  "sql plain">@ParentCode="sql string">''</div>
<div class="line number138 index137 alt1">"sql spaces"> </div>
<div class="line number139 index138 alt2">"sql spaces"> </div>
<div class="line number140 index139 alt1">"sql keyword">Select "sql plain">@ParentCode=Code  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">ClassID=@ParentID </div>
<div class="line number141 index140 alt2">"sql keyword">Select "sql plain">@Code=Code  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">ClassID=@ClassID </div>
<div class="line number142 index141 alt1">"sql spaces"> </div>
<div class="line number143 index142 alt2">"sql spaces"> </div>
<div class="line number144 index143 alt1">"sql comments">--修改原有类别  </div>
<div class="line number145 index144 alt2">"sql comments">--确定是否要修改Code字段 </div>
<div class="line number146 index145 alt1">"sql comments">--查看是否改变了直接父类别(上一级) </div>
<div class="line number147 index146 alt2">"sql plain">If @ParentCode  != "sql color2">Left"sql plain">(@code,len(@code)-@StepLen) </div>
<div class="line number148 index147 alt1">"sql spaces">    "sql keyword">Begin</div>
<div class="line number149 index148 alt2">"sql spaces">    "sql comments">--过滤选择自己做为父类  </div>
<div class="line number150 index149 alt1">"sql spaces">    "sql plain">If(@ParentCode !=@Code)  </div>
<div class="line number151 index150 alt2">"sql spaces">      "sql keyword">Begin</div>
<div class="line number152 index151 alt1">"sql spaces">        "sql comments">--过滤选择自己的子类为父类    </div>
<div class="line number153 index152 alt2">"sql spaces">        "sql plain">If Len(@ParentCode)  > Len(@Code) </div>
<div class="line number154 index153 alt1">"sql spaces">        "sql keyword">Begin</div>
<div class="line number155 index154 alt2">"sql spaces">           "sql comments">--因为  Len(@ParentCode) > Len(@Code) 所以可以Left(@ParentCode,Len(@Code)) </div>
<div class="line number156 index155 alt1">"sql spaces">           "sql plain">If "sql color2">Left"sql plain">(@ParentCode,Len(@Code))  != @Code "sql comments">--如果相等则为选择自己的子类为父类   </div>
<div class="line number157 index156 alt2">"sql spaces">            "sql keyword">Begin</div>
<div class="line number158 index157 alt1">"sql spaces">               "sql keyword">Set  "sql plain">@EditCode=1 </div>
<div class="line number159 index158 alt2">"sql spaces">            "sql keyword">End</div>
<div class="line number160 index159 alt1">"sql spaces">        "sql keyword">End</div>
<div class="line number161 index160 alt2">"sql spaces">        "sql keyword">Else</div>
<div class="line number162 index161 alt1">"sql spaces">        "sql keyword">Begin</div>
<div class="line number163 index162 alt2">"sql spaces">            "sql keyword">Set "sql plain">@EditCode=1 </div>
<div class="line number164 index163 alt1">"sql spaces">        "sql keyword">End</div>
<div class="line number165 index164 alt2">"sql spaces">      "sql keyword">End</div>
<div class="line number166 index165 alt1">"sql spaces">     </div>
<div class="line number167 index166 alt2">"sql spaces">    "sql keyword">End</div>
<div class="line number168 index167 alt1">"sql spaces"> </div>
<div class="line number169 index168 alt2">"sql spaces"> </div>
<div class="line number170 index169 alt1">"sql plain">If(@editCode=1) </div>
<div class="line number171 index170 alt2">"sql spaces">    "sql keyword">Begin</div>
<div class="line number172 index171 alt1">"sql spaces">        "sql comments">--获取子类中编号最大的Code,column.ParentCode + matchStr中  </div>
<div class="line number173 index172 alt2">"sql spaces">    "sql keyword">Select "sql keyword">Top "sql plain">1 @MyCode= Code  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">Code  "sql color1">Like "sql plain">@ParentCode  + @matchStr "sql keyword">Order "sql keyword">By "sql plain">Code "sql keyword">DESC</div>
<div class="line number174 index173 alt1">"sql spaces">    "sql comments">--是否有子类 </div>
<div class="line number175 index174 alt2">"sql spaces">    "sql plain">If @@ROWCOUNT >0  </div>
<div class="line number176 index175 alt1">"sql spaces">        "sql keyword">Begin</div>
<div class="line number177 index176 alt2">"sql spaces">        "sql keyword">Set "sql plain">@selfCode="sql color2">Cast"sql plain">("sql color2">Right"sql plain">(@MyCode,@StepLen) "sql keyword">As  "sql keyword">Int "sql plain">) +1 </div>
<div class="line number178 index177 alt1">"sql spaces">        "sql keyword">Set "sql plain">@typeCode=Replicate("sql string">'0'"sql plain">,@StepLen-1) + "sql color2">Cast"sql plain">(@selfCode "sql keyword">As "sql plain">nvarchar) </div>
<div class="line number179 index178 alt2">"sql spaces">                "sql keyword">Set  "sql plain">@typeCode="sql color2">Right"sql plain">(@typeCode,@StepLen)  </div>
<div class="line number180 index179 alt1">"sql spaces">                "sql keyword">Set  "sql plain">@typeCode=@ParentCode + @TypeCode </div>
<div class="line number181 index180 alt2">"sql spaces">        "sql keyword">End</div>
<div class="line number182 index181 alt1">"sql spaces">    "sql keyword">Else "sql comments">--没有子类那么编号从1开始 </div>
<div class="line number183 index182 alt2">"sql spaces">        "sql keyword">Begin</div>
<div class="line number184 index183 alt1">"sql spaces">        "sql keyword">Set "sql plain">@typeCode=@ParentCode +Replicate("sql string">'0'"sql plain">,@StepLen-1)+"sql string">'1'</div>
<div class="line number185 index184 alt2">"sql spaces">        "sql keyword">End</div>
<div class="line number186 index185 alt1">"sql spaces">    "sql keyword">End</div>
<div class="line number187 index186 alt2">"sql spaces"> </div>
<div class="line number188 index187 alt1">"sql plain">If  (@editCode=1) </div>
<div class="line number189 index188 alt2">"sql spaces"> "sql keyword">Begin</div>
<div class="line number190 index189 alt1">"sql spaces">   "sql keyword">Update "sql plain">[Category] "sql keyword">Set</div>
<div class="line number191 index190 alt2">"sql spaces">    "sql plain">ClassName=@ClassName,Code=@typeCode, Info=@Info </div>
<div class="line number192 index191 alt1">"sql spaces">   "sql keyword">where "sql plain">ClassID=@ClassID </div>
<div class="line number193 index192 alt2">"sql spaces"> "sql keyword">End</div>
<div class="line number194 index193 alt1">"sql keyword">Else</div>
<div class="line number195 index194 alt2">"sql spaces"> "sql keyword">Begin</div>
<div class="line number196 index195 alt1">"sql spaces">   "sql keyword">Update "sql plain">[Category] "sql keyword">Set</div>
<div class="line number197 index196 alt2">"sql spaces">     "sql plain">ClassName=@ClassName,  Info=@Info </div>
<div class="line number198 index197 alt1">"sql spaces">   "sql keyword">where "sql plain">ClassID=@ClassID      </div>
<div class="line number199 index198 alt2">"sql spaces"> "sql keyword">End</div>
<div class="line number200 index199 alt1">"sql comments">---修改子类编号(Code) </div>
<div class="line number201 index200 alt2">"sql plain">If(@editCode=1) </div>
<div class="line number202 index201 alt1">"sql spaces">   "sql keyword">Begin</div>
<div class="line number203 index202 alt2">"sql spaces">      "sql keyword">Update "sql plain">[Category] "sql keyword">Set</div>
<div class="line number204 index203 alt1">"sql spaces">       "sql plain">Code=@typeCode +  "sql color2">Right"sql plain">(Code,Len(Code)-Len(@Code)) </div>
<div class="line number205 index204 alt2">"sql spaces">      "sql keyword">Where "sql plain">Code "sql color1">Like "sql plain">@Code + "sql string">'%'</div>
<div class="line number206 index205 alt1">"sql spaces">   "sql keyword">End</div>
<div class="line number207 index206 alt2">"sql spaces">     </div>
<div class="line number208 index207 alt1">"sql plain">GO  </div>
<div class="line number209 index208 alt2">"sql spaces"> </div>
<div class="line number210 index209 alt1">"sql comments">--************************************ </div>
<div class="line number211 index210 alt2">"sql comments">--  删除一个分类，只允许删除没有子类的分类 </div>
<div class="line number212 index211 alt1">"sql comments">--************************************ </div>
<div class="line number213 index212 alt2">"sql spaces"> </div>
<div class="line number214 index213 alt1">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category_Del]'"sql plain">)  "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsProcedure'"sql plain">) = 1)  </div>
<div class="line number215 index214 alt2">"sql keyword">drop  "sql keyword">procedure "sql plain">[dbo].[Category_Del] </div>
<div class="line number216 index215 alt1">"sql plain">GO  </div>
<div class="line number217 index216 alt2">"sql spaces"> </div>
<div class="line number218 index217 alt1">"sql keyword">Create    "sql plain">Proc Category_Del  </div>
<div class="line number219 index218 alt2">"sql plain">@ClassID  "sql keyword">int</div>
<div class="line number220 index219 alt1">"sql keyword">As</div>
<div class="line number221 index220 alt2">"sql plain">If  ("sql keyword">Select "sql color2">Count"sql plain">(ClassID) "sql keyword">From"sql plain">[Category] "sql keyword">Where "sql plain">Code "sql color1">Like"sql plain">("sql keyword">Select "sql plain">Code "sql keyword">From "sql plain">[Category] "sql keyword">Where "sql plain">ClassID=@ClassID)+"sql string">'%'  "sql color1">And "sql plain">ClassId <>  @ClassId ) >0 </div>
<div class="line number222 index221 alt1">"sql spaces">    "sql keyword">Begin</div>
<div class="line number223 index222 alt2">"sql spaces">      "sql plain">RaisError ("sql string">'不能删除带有子类的分类'"sql plain">,16,1)  </div>
<div class="line number224 index223 alt1">"sql spaces">      "sql keyword">Return</div>
<div class="line number225 index224 alt2">"sql spaces">    "sql keyword">End</div>
<div class="line number226 index225 alt1">"sql spaces"> </div>
<div class="line number227 index226 alt2">"sql keyword">Declare "sql plain">@Code nvarchar(200)  </div>
<div class="line number228 index227 alt1">"sql keyword">Declare "sql plain">@Value "sql keyword">int</div>
<div class="line number229 index228 alt2">"sql keyword">Set  "sql plain">@Value=0 </div>
<div class="line number230 index229 alt1">"sql keyword">Select "sql plain">@Code=[Code],@Value=[DataNum] "sql keyword">From "sql plain">[Category] "sql keyword">Where "sql plain">[ClassID]=@ClassID  </div>
<div class="line number231 index230 alt2">"sql keyword">Update "sql plain">[Category]  "sql keyword">Set "sql plain">[DataNum]=[DataNum] - @Value "sql keyword">Where "sql plain">[ClassID] "sql color1">In"sql plain">( "sql keyword">Select "sql plain">ClassID "sql keyword">From "sql plain">[Category] "sql keyword">Where "sql plain">Len(Code)<=Len(@Code) "sql color1">And "sql plain">Code="sql color2">Left"sql plain">(@Code,Len(Code)))  </div>
<div class="line number232 index231 alt1">"sql keyword">Delete "sql keyword">From "sql plain">Category  "sql keyword">Where "sql plain">ClassID=@ClassID   </div>
<div class="line number233 index232 alt2">"sql spaces"> </div>
<div class="line number234 index233 alt1">"sql plain">Go  </div>
<div class="line number235 index234 alt2">"sql spaces"> </div>
<div class="line number236 index235 alt1">"sql comments">--************************** </div>
<div class="line number237 index236 alt2">"sql comments">--  根据编号获取一条分类记录 </div>
<div class="line number238 index237 alt1">"sql comments">--*************************** </div>
<div class="line number239 index238 alt2">"sql spaces"> </div>
<div class="line number240 index239 alt1">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category_Select]'"sql plain">)  "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsProcedure'"sql plain">) = 1)  </div>
<div class="line number241 index240 alt2">"sql keyword">drop  "sql keyword">procedure "sql plain">[dbo].[Category_Select] </div>
<div class="line number242 index241 alt1">"sql plain">GO  </div>
<div class="line number243 index242 alt2">"sql spaces"> </div>
<div class="line number244 index243 alt1">"sql keyword">Create  "sql keyword">PROCEDURE  "sql plain">Category_Select </div>
<div class="line number245 index244 alt2">"sql spaces">    "sql plain">@ClassID "sql keyword">int</div>
<div class="line number246 index245 alt1">"sql keyword">AS</div>
<div class="line number247 index246 alt2">"sql keyword">SELECT "sql plain">[ClassID],[ClassName],[Code],[DataNum], [Info] </div>
<div class="line number248 index247 alt1">"sql spaces"> </div>
<div class="line number249 index248 alt2">"sql keyword">FROM  "sql plain">[Category]  </div>
<div class="line number250 index249 alt1">"sql keyword">WHERE</div>
<div class="line number251 index250 alt2">"sql spaces">    "sql plain">[ClassID]=@ClassID  </div>
<div class="line number252 index251 alt1">"sql plain">Go    </div>
<div class="line number253 index252 alt2">"sql spaces"> </div>
<div class="line number254 index253 alt1">"sql spaces"> </div>
<div class="line number255 index254 alt2">"sql comments">--************************** </div>
<div class="line number256 index255 alt1">"sql comments">-- 移动分类的排序  </div>
<div class="line number257 index256 alt2">"sql comments">--******************************* </div>
<div class="line number258 index257 alt1">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category_Move]'"sql plain">)  "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsProcedure'"sql plain">) = 1)  </div>
<div class="line number259 index258 alt2">"sql keyword">drop  "sql keyword">procedure "sql plain">[dbo].[Category_Move] </div>
<div class="line number260 index259 alt1">"sql plain">Go  </div>
<div class="line number261 index260 alt2">"sql spaces"> </div>
<div class="line number262 index261 alt1">"sql keyword">Create     "sql plain">Proc Category_Move  </div>
<div class="line number263 index262 alt2">"sql plain">@ClassID  "sql keyword">int"sql plain">,  </div>
<div class="line number264 index263 alt1">"sql plain">@IsUp  "sql keyword">bit"sql plain">=1  </div>
<div class="line number265 index264 alt2">"sql keyword">As</div>
<div class="line number266 index265 alt1">"sql keyword">Declare "sql plain">@maskStr  nvarchar(200) </div>
<div class="line number267 index266 alt2">"sql keyword">Declare "sql plain">@tempStr  nvarchar(200) </div>
<div class="line number268 index267 alt1">"sql keyword">Declare "sql plain">@Code nvarchar(200)  </div>
<div class="line number269 index268 alt2">"sql keyword">Set  "sql plain">@Code="sql string">''</div>
<div class="line number270 index269 alt1">"sql keyword">Set  "sql plain">@tempStr="sql string">''</div>
<div class="line number271 index270 alt2">"sql keyword">Select "sql plain">@Code=Code  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">ClassID=@ClassID </div>
<div class="line number272 index271 alt1">"sql keyword">Set  "sql plain">@maskStr=REPLICATE(N"sql string">'-'"sql plain">,Len(@Code)) </div>
<div class="line number273 index272 alt2">"sql plain">If  @Code  !="sql string">'' "sql color1">And  "sql plain">( (Len(@Code) % 4) =0 ) </div>
<div class="line number274 index273 alt1">"sql spaces">   "sql keyword">Begin</div>
<div class="line number275 index274 alt2">"sql spaces">     "sql plain">If(@isUp=1) </div>
<div class="line number276 index275 alt1">"sql spaces">       "sql keyword">Begin</div>
<div class="line number277 index276 alt2">"sql spaces">         "sql plain">If(Len(@Code) > 4)  </div>
<div class="line number278 index277 alt1">"sql spaces">           "sql keyword">Begin</div>
<div class="line number279 index278 alt2">"sql spaces">             "sql keyword">Select  "sql keyword">Top "sql plain">1 @tempStr=Code  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">Len(Code)=Len(@Code) "sql color1">And "sql plain">Code < @Code  "sql color1">And "sql color2">Left"sql plain">(Code,Len(Code)-4)="sql color2">Left"sql plain">(@Code,Len(@Code)-4)  "sql keyword">Order "sql keyword">By "sql plain">Code "sql keyword">DESC</div>
<div class="line number280 index279 alt1">"sql spaces">           "sql keyword">End</div>
<div class="line number281 index280 alt2">"sql spaces">         "sql keyword">Else</div>
<div class="line number282 index281 alt1">"sql spaces">           "sql keyword">Begin</div>
<div class="line number283 index282 alt2">"sql spaces">             "sql keyword">Select  "sql keyword">Top "sql plain">1  @tempStr=Code  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">Len(Code)=Len(@Code) "sql color1">And "sql plain">Code < @Code   "sql keyword">Order "sql keyword">By "sql plain">Code "sql keyword">DESC</div>
<div class="line number284 index283 alt1">"sql spaces">           "sql keyword">End</div>
<div class="line number285 index284 alt2">"sql spaces">       "sql keyword">End</div>
<div class="line number286 index285 alt1">"sql spaces">     "sql keyword">Else</div>
<div class="line number287 index286 alt2">"sql spaces">       "sql keyword">Begin</div>
<div class="line number288 index287 alt1">"sql spaces">         "sql plain">If(Len(@Code) >4)  </div>
<div class="line number289 index288 alt2">"sql spaces">           "sql keyword">Begin</div>
<div class="line number290 index289 alt1">"sql spaces">         "sql keyword">Select "sql keyword">Top "sql plain">1 @tempStr=Code  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">Len(Code)=Len(@Code) "sql color1">And "sql plain">Code > @Code   "sql color1">And "sql color2">Left"sql plain">(Code,Len(Code)-4)="sql color2">Left"sql plain">(@Code,Len(@Code)-4)  "sql keyword">Order "sql keyword">By "sql plain">Code "sql keyword">ASC</div>
<div class="line number291 index290 alt2">"sql spaces">           "sql keyword">End</div>
<div class="line number292 index291 alt1">"sql spaces">         "sql keyword">Else</div>
<div class="line number293 index292 alt2">"sql spaces">           "sql keyword">Begin</div>
<div class="line number294 index293 alt1">"sql spaces">         "sql keyword">Select "sql keyword">Top "sql plain">1 @tempStr=Code  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">Len(Code)=Len(@Code) "sql color1">And "sql plain">Code >@Code  "sql keyword">Order "sql keyword">By  "sql plain">Code "sql keyword">ASC</div>
<div class="line number295 index294 alt2">"sql spaces">           "sql keyword">End</div>
<div class="line number296 index295 alt1">"sql spaces">       "sql keyword">End</div>
<div class="line number297 index296 alt2">"sql spaces">   "sql keyword">End</div>
<div class="line number298 index297 alt1">"sql comments">--  //已经是最前(最后) </div>
<div class="line number299 index298 alt2">"sql plain">If @tempStr  "sql keyword">Is "sql color1">Null  "sql color1">Or "sql plain">RTrim(LTrim(@tempStr))="sql string">''</div>
<div class="line number300 index299 alt1">"sql keyword">Begin</div>
<div class="line number301 index300 alt2">"sql spaces"> "sql keyword">return</div>
<div class="line number302 index301 alt1">"sql keyword">End</div>
<div class="line number303 index302 alt2">"sql spaces"> </div>
<div class="line number304 index303 alt1">"sql keyword">Declare "sql plain">@CodeLen "sql keyword">int</div>
<div class="line number305 index304 alt2">"sql keyword">Declare "sql plain">@MAXLEN "sql keyword">int</div>
<div class="line number306 index305 alt1">"sql keyword">Set  "sql plain">@CodeLen=Len(@Code) </div>
<div class="line number307 index306 alt2">"sql keyword">Set  "sql plain">@MAXLEN=200 </div>
<div class="line number308 index307 alt1">"sql comments">--//设置目标类，以及目标类的子类为----0001(目标类)或----00010002(子类)为形式  </div>
<div class="line number309 index308 alt2">"sql keyword">Update "sql plain">[Category]  "sql keyword">Set "sql plain">Code=@maskStr +"sql color2">Substring"sql plain">(code,@CodeLen  +1,@MAXLEN) "sql keyword">Where "sql color2">Left"sql plain">(code,@CodeLen)=@tempStr  </div>
<div class="line number310 index309 alt1">"sql comments">--//更新当前交换类(包括子类)Code为目标类Code </div>
<div class="line number311 index310 alt2">"sql keyword">Update "sql plain">[Category]  "sql keyword">Set "sql plain">Code=@tempStr +"sql color2">Substring"sql plain">(Code,@CodeLen+1,@MAXLEN) "sql keyword">Where "sql color2">Left"sql plain">(code,@CodeLen)=@Code </div>
<div class="line number312 index311 alt1">"sql comments">--//更新目标类（包括子类)Code为当前交换类Code </div>
<div class="line number313 index312 alt2">"sql keyword">Update "sql plain">[Category]  "sql keyword">Set "sql plain">Code=@Code  +"sql color2">Substring"sql plain">(code,@CodeLen +1,@MAXLEN) "sql keyword">Where "sql color2">Left"sql plain">(code,@CodeLen)=@maskStr </div>
<div class="line number314 index313 alt1">"sql spaces"> </div>
<div class="line number315 index314 alt2">"sql plain">Go  </div>
<div class="line number316 index315 alt1">"sql spaces"> </div>
<div class="line number317 index316 alt2">"sql comments">--**************************** </div>
<div class="line number318 index317 alt1">"sql comments">--获取指定分类的父分类信息 </div>
<div class="line number319 index318 alt2">"sql comments">--***************************** </div>
<div class="line number320 index319 alt1">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category_QueryParent]'"sql plain">) "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsProcedure'"sql plain">) = 1)  </div>
<div class="line number321 index320 alt2">"sql keyword">drop  "sql keyword">procedure "sql plain">[dbo].[Category_QueryParent] </div>
<div class="line number322 index321 alt1">"sql plain">Go  </div>
<div class="line number323 index322 alt2">"sql spaces"> </div>
<div class="line number324 index323 alt1">"sql keyword">Create   "sql plain">Proc  Category_QueryParent </div>
<div class="line number325 index324 alt2">"sql plain">@ClassID  "sql keyword">int</div>
<div class="line number326 index325 alt1">"sql keyword">As</div>
<div class="line number327 index326 alt2">"sql keyword">Declare "sql plain">@ClassCode  nvarchar(200) </div>
<div class="line number328 index327 alt1">"sql keyword">Select "sql plain">@ClassCode=Code  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">ClassId=@ClassID </div>
<div class="line number329 index328 alt2">"sql keyword">Select "sql plain">ClassID,ClassName,Code, DataNum </div>
<div class="line number330 index329 alt1">"sql spaces">       "sql keyword">From "sql plain">[Category] </div>
<div class="line number331 index330 alt2">"sql spaces">       "sql keyword">Where  "sql plain">Len(Code)<=Len(@ClassCode) </div>
<div class="line number332 index331 alt1">"sql spaces">       "sql color1">And "sql plain">Code = "sql color2">Left"sql plain">(@ClassCode,Len(Code)) </div>
<div class="line number333 index332 alt2">"sql spaces">       "sql keyword">Order "sql keyword">By "sql plain">Code  </div>
<div class="line number334 index333 alt1">"sql spaces">        </div>
<div class="line number335 index334 alt2">"sql spaces">        </div>
<div class="line number336 index335 alt1">"sql plain">Go  </div>
<div class="line number337 index336 alt2">"sql spaces"> </div>
<div class="line number338 index337 alt1">"sql comments">--****************************** </div>
<div class="line number339 index338 alt2">"sql comments">-- 获取整个分类目录  </div>
<div class="line number340 index339 alt1">"sql comments">--****************************** </div>
<div class="line number341 index340 alt2">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category_Query]'"sql plain">)  "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsProcedure'"sql plain">) = 1)  </div>
<div class="line number342 index341 alt1">"sql keyword">drop  "sql keyword">procedure "sql plain">[dbo].[Category_Query] </div>
<div class="line number343 index342 alt2">"sql plain">Go  </div>
<div class="line number344 index343 alt1">"sql spaces"> </div>
<div class="line number345 index344 alt2">"sql keyword">Create "sql plain">Proc Category_Query  </div>
<div class="line number346 index345 alt1">"sql keyword">As</div>
<div class="line number347 index346 alt2">"sql keyword">Select "sql plain">[ClassID],[ClassName],[Code], [DataNum] "sql keyword">From "sql plain">[Category] "sql keyword">Order "sql keyword">By "sql plain">[Code] </div>
<div class="line number348 index347 alt1">"sql plain">Go  </div>
<div class="line number349 index348 alt2">"sql spaces"> </div>
<div class="line number350 index349 alt1">"sql spaces"> </div>
<div class="line number351 index350 alt2">"sql comments">--***************************** </div>
<div class="line number352 index351 alt1">"sql comments">--重置所有分类为根分类 </div>
<div class="line number353 index352 alt2">"sql comments">--***************************** </div>
<div class="line number354 index353 alt1">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category_Reset]'"sql plain">)  "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsProcedure'"sql plain">) = 1)  </div>
<div class="line number355 index354 alt2">"sql keyword">drop  "sql keyword">procedure "sql plain">[dbo].[Category_Reset] </div>
<div class="line number356 index355 alt1">"sql plain">Go  </div>
<div class="line number357 index356 alt2">"sql spaces"> </div>
<div class="line number358 index357 alt1">"sql keyword">Create  "sql plain">Proc Category_Reset  </div>
<div class="line number359 index358 alt2">"sql keyword">As</div>
<div class="line number360 index359 alt1">"sql keyword">Declare "sql plain">@code nvarchar(200)  </div>
<div class="line number361 index360 alt2">"sql keyword">Declare "sql plain">@i "sql keyword">int</div>
<div class="line number362 index361 alt1">"sql keyword">Set  "sql plain">@Code="sql string">''</div>
<div class="line number363 index362 alt2">"sql keyword">Set  "sql plain">@i=1 </div>
<div class="line number364 index363 alt1">"sql spaces"> </div>
<div class="line number365 index364 alt2">"sql spaces"> </div>
<div class="line number366 index365 alt1">"sql keyword">Declare "sql plain">Category_Cursor  "sql keyword">CURSOR "sql keyword">For</div>
<div class="line number367 index366 alt2">"sql keyword">Select "sql plain">CODE "sql keyword">From "sql plain">[Category] </div>
<div class="line number368 index367 alt1">"sql spaces"> </div>
<div class="line number369 index368 alt2">"sql keyword">Open  "sql plain">Category_Cursor </div>
<div class="line number370 index369 alt1">"sql keyword">Fetch  "sql keyword">Next "sql keyword">From "sql plain">Category_Cursor  </div>
<div class="line number371 index370 alt2">"sql plain">WHILE  @@FETCH_STATUS=0 </div>
<div class="line number372 index371 alt1">"sql keyword">Begin</div>
<div class="line number373 index372 alt2">"sql spaces"> "sql keyword">Set "sql plain">@Code=Replicate(N"sql string">'0'"sql plain">,4) +  "sql color2">Cast"sql plain">(@i "sql keyword">as "sql plain">nvarchar) </div>
<div class="line number374 index373 alt1">"sql spaces"> "sql keyword">Set "sql plain">@Code="sql color2">Right"sql plain">(@Code,4) </div>
<div class="line number375 index374 alt2">"sql spaces"> "sql keyword">Update "sql plain">[Category]   "sql keyword">Set "sql plain">Code= @Code   "sql keyword">WHERE "sql keyword">Current "sql keyword">Of "sql plain">Category_Cursor </div>
<div class="line number376 index375 alt1">"sql spaces"> "sql keyword">Set "sql plain">@i=@i+1 </div>
<div class="line number377 index376 alt2">"sql spaces"> "sql keyword">Fetch "sql keyword">Next "sql keyword">From "sql plain">Category_Cursor  </div>
<div class="line number378 index377 alt1">"sql keyword">End</div>
<div class="line number379 index378 alt2">"sql keyword">Close  "sql plain">Category_Cursor </div>
<div class="line number380 index379 alt1">"sql keyword">DEALLOCATE "sql plain">Category_Cursor  </div>
<div class="line number381 index380 alt2">"sql spaces"> </div>
<div class="line number382 index381 alt1">"sql plain">Go  </div>
<div class="line number383 index382 alt2">"sql spaces"> </div>
<div class="line number384 index383 alt1">"sql comments">--********************* </div>
<div class="line number385 index384 alt2">"sql comments">--  获取指定分类的分类名称 </div>
<div class="line number386 index385 alt1">"sql comments">--************************ </div>
<div class="line number387 index386 alt2">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category_SelectClassName]'"sql plain">) "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsProcedure'"sql plain">) = 1)  </div>
<div class="line number388 index387 alt1">"sql keyword">drop  "sql keyword">procedure "sql plain">[dbo].[Category_SelectClassName] </div>
<div class="line number389 index388 alt2">"sql plain">Go  </div>
<div class="line number390 index389 alt1">"sql spaces"> </div>
<div class="line number391 index390 alt2">"sql keyword">Create  "sql plain">Proc  Category_SelectClassName </div>
<div class="line number392 index391 alt1">"sql plain">@ClassID  "sql keyword">int</div>
<div class="line number393 index392 alt2">"sql keyword">AS</div>
<div class="line number394 index393 alt1">"sql keyword">Select "sql plain">[ClassName]  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">[ClassID]=@ClassID </div>
<div class="line number395 index394 alt2">"sql plain">Go  </div>
<div class="line number396 index395 alt1">"sql spaces"> </div>
<div class="line number397 index396 alt2">"sql comments">--******************** </div>
<div class="line number398 index397 alt1">"sql comments">--  获取指定类的子类，并包括自身 </div>
<div class="line number399 index398 alt2">"sql comments">--********************* </div>
<div class="line number400 index399 alt1">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category_QueryChildren]'"sql plain">) "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsProcedure'"sql plain">) = 1)  </div>
<div class="line number401 index400 alt2">"sql keyword">drop  "sql keyword">procedure "sql plain">[dbo].[Category_QueryChildren] </div>
<div class="line number402 index401 alt1">"sql plain">Go  </div>
<div class="line number403 index402 alt2">"sql spaces"> </div>
<div class="line number404 index403 alt1">"sql keyword">Create "sql plain">Proc  Category_QueryChildren </div>
<div class="line number405 index404 alt2">"sql plain">@ClassID  "sql keyword">int</div>
<div class="line number406 index405 alt1">"sql keyword">As</div>
<div class="line number407 index406 alt2">"sql keyword">Declare "sql plain">@Code nvarchar(200)  </div>
<div class="line number408 index407 alt1">"sql keyword">Select "sql plain">@Code=[Code]  "sql keyword">From "sql plain">[Category]  "sql keyword">Where "sql plain">[ClassID]=@ClassID </div>
<div class="line number409 index408 alt2">"sql keyword">Select "sql plain">[ClassID],[ClassName],[Code], [DataNum] </div>
<div class="line number410 index409 alt1">"sql spaces">  "sql keyword">From "sql plain">[Category] "sql keyword">Where "sql plain">Code "sql color1">Like "sql plain">@Code +"sql string">'%' "sql keyword">Order "sql keyword">By "sql plain">Code       </div>
<div class="line number411 index410 alt2">"sql spaces">   </div>
<div class="line number412 index411 alt1">"sql plain">Go  </div>
<div class="line number413 index412 alt2">"sql spaces"> </div>
<div class="line number414 index413 alt1">"sql comments">--********************** </div>
<div class="line number415 index414 alt2">"sql comments">-- 获取一级分类列表  </div>
<div class="line number416 index415 alt1">"sql comments">--*********************** </div>
<div class="line number417 index416 alt2">"sql plain">if exists  ("sql keyword">select "sql plain">*  "sql keyword">from "sql plain">dbo.sysobjects "sql keyword">where  "sql plain">id = object_id(N"sql string">'[dbo].[Category_QueryRoot]'"sql plain">)  "sql color1">and "sql plain">OBJECTPROPERTY(id, N"sql string">'IsProcedure'"sql plain">) = 1)  </div>
<div class="line number418 index417 alt1">"sql keyword">drop  "sql keyword">procedure "sql plain">[dbo].[Category_QueryRoot] </div>
<div class="line number419 index418 alt2">"sql plain">Go  </div>
<div class="line number420 index419 alt1">"sql spaces"> </div>
<div class="line number421 index420 alt2">"sql keyword">Create  "sql plain">Proc  Category_QueryRoot </div>
<div class="line number422 index421 alt1">"sql keyword">AS</div>
<div class="line number423 index422 alt2">"sql keyword">Select "sql plain">[ClassID],[ClassName],[Code], [DataNum] "sql keyword">From "sql plain">[Category] "sql keyword">Where "sql plain">Len(Code)=4  "sql keyword">Order "sql keyword">By "sql plain">Code    </div>
<div class="line number424 index423 alt1">"sql spaces"> </div>
<div class="line number425 index424 alt2">"sql plain">Go</div>
</div>
</div>
试试其它关键字
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)