??????????????????

?????.???SQL???????
????1.???????where???????д???@var in…or(@var=”)?????
????2.where??????like‘%’+@var+’%’
????3.where??????case…end????
????4.??????????????????????????????????????????????????????
????5.SQL?????*???????????*?????
??????.??????
????????????洢?????д????????????
??????????????????????????????(?????????????????????)???????????????????????鵽????????????????????????????????ж???????????????????????????????????????????????????????

????????????????????????????
????1.????????????=@var or(@var=”)???ж??
????2.????sql???????????????
??????????????д?洢??????????????????
????1.??????????????????????????裻
????2.????in??not in??????exists??not exists?????
????3.??????????????????б????????б???????like???
????4.??????????????????????????
????5.????“*”???
????6.??????where?????ж???ν??к?????????
????7.???????????????????????(with(nolock))??
??????.?洢????
?????????ο?????????ε??????????????SQL????
????/**
????*?????????
????**/
????--exec spName1''??''??''??''??''??''??'???????'
????CREATE Procedure spName1
????@MESOrderID nvarchar(320)??--?????????30??
????@LotName nvarchar(700)??--??????к????50??
????@DateCode nvarchar(500)??--????????κ????30??
????@BatchID nvarchar(700)??--????????к?/???????????50??
????@comdef nvarchar(700)??--??????????????30??
????@SNCust nvarchar(1600)??--?????к????50??
????@OnPlant nvarchar(20)--??
????AS
????BEGIN
????SET NOCOUNT ON;
????/**
????*1)??????????????????????????????????????????ó????????
????**/
????CREATE TABLE#FinalLotName
????(
????LotName NVARCHAR(50)??--???к?
????SourceLotName NVARCHAR(50)??--??????к?
????SNCust NVARCHAR(128)--?????к?
????)
????--1.1
????IF@LotName<>''
????BEGIN
????SELECT Val INTO#WorkLot FROM fn_String_To_Table(@LotName??'??'??1)
????SELECT LotPK??LotName INTO#WorkLotPK FROM MMLots WITH(NOLOCK)WHERE EXISTS(SELECT 1 FROM#WorkLot b WHERE b.Val=MMLots.LotID)
????--??SourceLotPK???????????
????SELECT a.LotPK??a.SourceLotPK into#WorkSourcePK FROM MMLotOperations a WITH(NOLOCK)WHERE EXISTS(SELECT 1 FROM#WorkLotPK b WHERE b.LotPK=a.LotPK)AND a.SourceLotPK IS NOT NULL
????SELECT a.LotPK??a.SourceLotPK??b.LotName INTO#WorkSourcePK2 FROM#WorkSourcePK a JOIN#WorkLotPK b ON a.LotPK=b.LotPK
????INSERT INTO#FinalLotName SELECT a.LotName??b.LotName AS SourceLotName??NULL FROM#WorkSourcePK2 a JOIN(SELECT LotPK??LotName FROM MMLots WITH(NOLOCK))b on a.SourceLotPK=b.LotPK--b??????????WHERE RowDeleted=0?????
????SELECT a.LotName??a.SourceLotName??b.SNCust INTO#FinalLotNameX1 FROM#FinalLotName a LEFT JOIN CO_SN_LINK_CUSTOMER b WITH(NOLOCK)ON a.LotName=b.SNMes
????DELETE FROM#FinalLotName
????INSERT INTO#FinalLotName SELECT LotName??SourceLotName??SNCust FROM#FinalLotNameX1
????END
????--1.2
????IF@BatchID<>''
????BEGIN
????SELECT Val INTO#WorkSourceLot FROM fn_String_To_Table(@BatchID??'??'??1)
????IF EXISTS(SELECT 1 FROM#FinalLotName)--???@LotName??????
????BEGIN
????SELECT a.LotName??a.SourceLotName??a.SNCust INTO#FinalLotNameX2 FROM#FinalLotName a WHERE EXISTS(SELECT 1 FROM#WorkSourceLot b WHERE a.SourceLotName=b.Val)
????DELETE FROM#FinalLotName
????INSERT INTO#FinalLotName SELECT LotName??SourceLotName??SNCust FROM#FinalLotNameX2
????END
????ELSE--@LotName???????
????BEGIN
????SELECT LotPK AS SourceLotPK??LotName AS SourceLotName INTO#2 FROM MMLots WITH(NOLOCK)WHERE EXISTS(SELECT 1 FROM#WorkSourceLot b WHERE b.Val=MMLots.LotID)
????SELECT a.LotPK??a.SourceLotPK into#21 FROM MMLotOperations a WITH(NOLOCK)WHERE EXISTS(SELECT 1 FROM#2 b WHERE b.SourceLotPK=a.SourceLotPK)
????SELECT a.LotPK??a.SourceLotPK??b.SourceLotName INTO#22 FROM#21 a JOIN#2 b ON a.SourceLotPK=b.SourceLotPK
????INSERT INTO#FinalLotName SELECT b.LotName??a.SourceLotName??NULL FROM#22 a JOIN(SELECT LotPK??LotName FROM MMLots WITH(NOLOCK))b on a.LotPK=b.LotPK--b??????????WHERE RowDeleted=0?????
????SELECT a.LotName??a.SourceLotName??b.SNCust INTO#FinalLotNameX21 FROM#FinalLotName a LEFT JOIN CO_SN_LINK_CUSTOMER b WITH(NOLOCK)ON a.LotName=b.SNMes
????DELETE FROM#FinalLotName
????INSERT INTO#FinalLotName SELECT LotName??SourceLotName??SNCust FROM#FinalLotNameX21
????END
????END
????--1.3
????IF@SNCust<>''
????BEGIN
????SELECT Val INTO#WorkCustomSN FROM fn_String_To_Table(@SNCust??'??'??1)
????IF EXISTS(SELECT 1 FROM#FinalLotName)--???????????????????????
????BEGIN
????SELECT a.LotName??a.SourceLotName??a.SNCust INTO#FinalLotNameX3 FROM#FinalLotName a WHERE EXISTS(SELECT 1 FROM#WorkCustomSN b WHERE a.SNCust=b.Val)
????DELETE FROM#FinalLotName
????INSERT INTO#FinalLotName SELECT LotName??SourceLotName??SNCust FROM#FinalLotNameX3
????END
????ELSE
????BEGIN
????SELECT a.SNMes INTO#WorkLotX FROM CO_SN_LINK_CUSTOMER a WITH(NOLOCK)WHERE EXISTS(SELECT 1 FROM#WorkCustomSN b WHERE a.SNCust=b.Val)
????-------------------????????????1(@LotName)????[??????????к???????к????????????ж????????]
????SELECT LotPK??LotName INTO#WorkLotPKX FROM MMLots WITH(NOLOCK)WHERE EXISTS(SELECT 1 FROM#WorkLotX b WHERE b.SNMes=MMLots.LotID)
????--??SourceLotPK???????????
????SELECT a.LotPK??a.SourceLotPK into#WorkSourcePKX FROM MMLotOperations a WITH(NOLOCK)WHERE EXISTS(SELECT 1 FROM#WorkLotPKX b WHERE b.LotPK=a.LotPK)AND a.SourceLotPK IS NOT NULL
????SELECT a.LotPK??a.SourceLotPK??b.LotName INTO#WorkSourcePK2X FROM#WorkSourcePKX a JOIN#WorkLotPKX b ON a.LotPK=b.LotPK
????INSERT INTO#FinalLotName SELECT a.LotName??b.LotName AS SourceLotName??NULL FROM#WorkSourcePK2X a JOIN(SELECT LotPK??LotName FROM MMLots WITH(NOLOCK))b on a.SourceLotPK=b.LotPK--b??????????WHERE RowDeleted=0?????
????SELECT a.LotName??a.SourceLotName??b.SNCust INTO#FinalLotNameX31 FROM#FinalLotName a LEFT JOIN CO_SN_LINK_CUSTOMER b WITH(NOLOCK)ON a.LotName=b.SNMes
????DELETE FROM#FinalLotName
????INSERT INTO#FinalLotName SELECT LotName??SourceLotName??SNCust FROM#FinalLotNameX31
????-----------------------
????END
????END
????/**
????*2)???????????????????滻??????????????
????**/
????CREATE TABLE#FinalCO_SN
????(
????SN NVARCHAR(50)??
????SourceSN NVARCHAR(50)??
????SNCust NVARCHAR(128)??
????matl_def_id NVARCHAR(50)??--sn??????ID
????ComMaterials NVARCHAR(50)??--SourceSN??????ID
????MESOrderID NVARCHAR(20)??
????OnPlantID NVARCHAR(20)??
????VendorID NVARCHAR(20)??
????DateCode NVARCHAR(20)??
????SNNote NVARCHAR(512)
????)
????--2.1
????IF@MESOrderID<>''
????BEGIN
????-------------------------------??MESOrderID????????-----------------------------------
????SELECT Val INTO#WorkMESOrderID FROM fn_String_To_Table(@MESOrderID??'??'??1)
????IF@OnPlant='Comba'
????BEGIN
????UPDATE#WorkMESOrderID SET Val='C000'+Val WHERE LEN(Val)=9
????END
????ELSE
????BEGIN
????UPDATE#WorkMESOrderID SET Val='W000'+Val WHERE LEN(Val)=9
????END
????SELECT SN??MaterialID??MESOrderID??OnPlantID INTO#WorkCO_SN1 FROM CO_SN_GENERATION a WITH(NOLOCK)
????WHERE SNType='IntSN'AND SNRuleName='ProductSNRule'AND OnPlantID=@OnPlant
????AND EXISTS(SELECT 1 FROM#WorkMESOrderID b WHERE a.MESOrderID=b.Val)
????------------------------------------------------------------------------------------------