Code Tips 2

1.unpivot

with T(LOTNO, IDName, DepartmentCode, OperateType, State, ModTime, C1_001_001, C1_001_002, [Value], CreateTime, GetTime, FinishTime, Status)
as
(
select lower(newid()),IDName,@DepartmentCode,0,0,getdate(),C1_001_001,C1_001_002,[Value],getdate(),null,null,'00' from
(
select JC_CYXX.SFZHM as C1_001_001 , JC_CYXX.CYXM as C1_001_002 ,
convert(nvarchar(max),JC_CYXX.SFZHM) as SFZHM,convert(nvarchar(max),JC_CBXX.CBBZ) as CBBZ,
convert(nvarchar(max),JC_CBXX.YXBZ) as YXBZ,convert(nvarchar(max),JC_JTXX.XZDM) as XZDM,
convert(nvarchar(max),JC_JTXX.XZCDM) as XZCDM,convert(nvarchar(max),JC_JTXX.JCDM) as JCDM
from JC_CBXX
left join JC_CYXX on JC_CBXX.HZYLHM = JC_CYXX.HZYLHM
left join JC_JTXX on JC_JTXX.JCDM = JC_JTXX.JCDM  and JC_JTXX.JTHM = JC_CYXX.JTHM  and JC_CYXX.JTHM = JC_JTXX.JTHM
where 1!=2
) p
unpivot
(
[Value] for IDName in (SFZHM,CBBZ,YXBZ,XZDM,XZCDM,JCDM)) as unpt
)
insert into TempTable select * from T

Notes
--1.selected column can not appeared in the unpivot col list,if you want to, please use alias
--2.all of the column specified in the unpivot list must have same datatype and same data size

 

2.SQL 2005 left/right join 问题

原来2000里有时图方便这样写left/right join

select a.a,b.b from tableA a,tableB b where a.a*=b.b

但是在2005里这样写就不对了，

select a.a,b.b from tableA a left join tableB b on a.a=b.b

如果有2000的库升级到2005，又不想修改sql的话,如果没有用到2005的新特性的话，可以这样修改

EXEC sp_dbcmptlevel <DBName>, 80;
GO

3.查看临时表存在

if object_id('tempdb..#TC_TableRelation') is null
begin
    CREATE TABLE [#TC_TableRelation](
        [ID] [nvarchar](255) NOT NULL,
        [ViewName] [nvarchar](50) NOT NULL,
        [MasterTableName] [nvarchar](255) NULL,
        [PrimaryKey] [nvarchar](50) NOT NULL,
        [PrimaryValue] [nvarchar](255) NULL,
        [Status] [nvarchar](255) NULL
    ) ON [PRIMARY]
end

或者

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#TC_TableRelation') and type='U')
   drop table #TC_TableRelation

4.存储过程是不支持Table类型参数的,解决办法

a).用游标变量代替

b).使用2005里的xml数据类型

   Ref:Passing a Table to A Stored Procedure