sql server 2000更新技巧&select 查询中带参数，游标中使用exec

更新多个ID最后时间的记录

update  a set qesl_no=0 from f_gxhz a where fbid_no in ('117887','117889','118112') and

cjsj_date = (select max(cjsj_date) from f_gxhz b where a.fbid_no=b.fbid_no)

update中使用case when

UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END
WHERE id IN (1,2,3)

select 查询中带参数

begin
select @DateShiJiaBegin=convert(varchar(7),''+@DateFrom+'',20)+'-01'
SELECT @Diff=DATEDIFF(day, ''+@DateShiJiaBegin+'',''+@DateFrom+'')
print @Diff
end

 人员配置

ALTER      Proc dbo.dailybulletin_ManPowerRate
@DateFrom  nvarchar(50) ,
@DateTo  nvarchar(50),
@Shift  nvarchar(50)
AS
declare @sqlText nvarchar(4000)
declare @sqlWhere nvarchar(4000)
begin
if object_id('tempdb..##dailybulletin_ManPowerSum') is not null
drop table ##dailybulletin_ManPowerSum
set @sqlWhere='where (input_date between '''+@DateFrom+ ''' and '''+ @DateTo+''') '
if ((@Shift is not null) and (isnull(@Shift,'')<>''))
begin
set @sqlWhere=@sqlWhere+' and shift ='''+@Shift+''''
end
set @sqlWhere=@sqlWhere+'  group by workshop,station_type'
set @sqlText='select workshop,station_type,sum(real_manpowerQty) realQty  into ##dailybulletin_ManPowerSum  from dailybulletin_ManPowerAssign  '
exec (@sqlText+@sqlWhere)
set @sqlText='select workshop ''工位'''
select  @sqlText=@sqlText+', sum(case station_type when'''+station_type+''' Then realQty else 0 end) as '''+station_type +''''  from (select distinct station_type from ##dailybulletin_ManPowerSum) sub
set @sqlText=@sqlText+' from  ##dailybulletin_ManPowerSum  group by workshop'
exec (@sqlText)
end

 CREATE TABLE [dailybulletin_ManPowerAssign] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [input_date] [datetime]  ,
 [workshop] [nvarchar] (25)  ,
 [shift] [nvarchar] (15) ,
 [station_type] [nvarchar] (50) ,
 [station_detail] [nvarchar] (50) ,
 [standard_manpowerQty] [decimal](8, 2),
 [stationMachine_sum] [decimal](8, 2) ,
 [real_manpowerQty] [decimal](8, 2) ,
 [remark] [nvarchar] (1000)  ,
 [EmpNo] [nvarchar] (50) ,
 [update_time] [datetime] DEFAULT (getdate())
) ON [PRIMARY]
GO

 ------------游标中使用exec
ALTER procedure [dbo].[ND_Update]
@CurrND nvarchar(100),
@NextND nvarchar(100),
@Prefix nvarchar(100),
@ColumnName nvarchar(100),
@ColumnValue  nvarchar(100)
AS
begin
declare @sql nvarchar(2000)
declare @colName  nvarchar(100)
declare @currValue  nvarchar(100)

 set @sql=N'DECLARE col_cursor CURSOR FOR SELECT a.name FROM (Select name from syscolumns Where ID=OBJECT_ID(@CurrND))  a ,
    (Select name from syscolumns Where ID=OBJECT_ID(@NextND) ) b
    where a.name=b.name and a.name like @Prefix+''%'''
 EXEC SP_EXECUTESQL  @sql,N'@CurrND nvarchar(100),@NextND nvarchar(100),@Prefix nvarchar(100)',@CurrND,@NextND,@Prefix

OPEN col_cursor
FETCH NEXT FROM col_cursor into @colName
WHILE @@FETCH_STATUS = 0
BEGIN
   set @sql= N'select top 1 @currValue='+@colName+' from ' +@CurrND+' a where '+@ColumnName+'=@ColumnValue order by RDT DESC,OID desc'
   EXEC SP_EXECUTESQL @sql,N'@currValue  nvarchar(100) out,@colName  nvarchar(100),@ColumnValue  nvarchar(100)' ,@currValue out,@colName,@ColumnValue
   set @sql='update a set a.'+@colName+'=@currValue from (select top 1 * from '+@NextND+' a where a.'+@ColumnName+'=@ColumnValue order by OID ) a where '+@ColumnName+'=@ColumnValue'
   EXEC SP_EXECUTESQL @sql,N'@currValue nvarchar(100),@ColumnValue  nvarchar(100)' ,@currValue,@ColumnValue
   FETCH NEXT FROM col_cursor into @colName
END
CLOSE col_cursor
DEALLOCATE col_cursor
end