如何编写这个存储过程

Question

简单：
我给个例子：
/*创建每月的表及其触发器*/
CREATE PROCEDURE NEW_SP_STATION_DM_Create_MonthTable_Exit(@StationID Varchar(6),@TableYearMonth Varchar(6)) AS
Declare @TableSQL Varchar(6500)
BEGIN  /*1.生成该月的出口表*/
  Select @TableSQL='CREATE TABLE [dbo].[Exit'+@TableYearMonth+@StationID+'] ('+
                                   '[CardNetWork] [smallint] NOT NULL ,[CardID] [int] NOT NULL ,[EntryNetWork] [smallint] NOT NULL ,'+
                                   '[EntryStation] [int] NOT NULL ,[EntryLane] [tinyint] NOT NULL ,[EntryTime] [datetime] NOT NULL ,'+
                                   '[EntryDate] [tinyint] NOT NULL ,[EntryOperator] [int] NOT NULL ,[EntryShift] [tinyint] NOT NULL ,'+
                                   '[RVehicleClass] [tinyint] NOT NULL ,[RDealStatus] [smallint] NOT NULL ,[ExitNetWork] [smallint] NOT NULL ,'+
                                   '[ExitStation] [int] NOT NULL ,[ExitLane] [tinyint] NOT NULL ,[ExitTime] [datetime] NOT NULL ,'+
                                   '[ExitDate] [tinyint] NOT NULL ,[ExitOperator] [int] NOT NULL ,[ExitShift] [tinyint] NOT NULL ,'+
                                   '[VehicleClass] [tinyint] NOT NULL ,[PVehicleClass] [tinyint] NOT NULL ,[TotalToll] [smallint] NOT NULL ,'+
                                   '[RealToll] [smallint] NOT NULL ,[DealStatus] [smallint] NOT NULL ,[DeviceStatus] [smallint] NOT NULL ,'+
                                   '[OwnerNetwork] [smallint] NOT NULL ,[OwnerID] [tinyint] NOT NULL ,[OwnerToll] [smallint] NOT NULL ,'+
                                   '[RecordType] [tinyint] NOT NULL ,[FlagStations] [int] NOT NULL ,[RODSpecification] [tinyint] NOT NULL ,'+
                                   '[JudgeType] [tinyint] NOT NULL ,[JudgeOperator] [int] NOT NULL ,[EntryCarNumber] [varchar] (10) NOT NULL ,'+
                                   '[ExitCarNumber] [varchar] (10)  NOT NULL ,[Reserved1] [smallint] NOT NULL ,[Reserved2] [smallint] NOT NULL ,'+
                                   'CONSTRAINT [PK_Exit'+@TableYearMonth+@StationID+'] PRIMARY KEY  CLUSTERED'+
                                   '([ExitNetWork],[ExitStation],[ExitLane],[ExitTime])'+
                                   ')ON [PRIMARY]'    Exec (@TableSQL)

Answer 1

/*2.生成该表的触发器*/
    SET @TableSQL='CREATE TRIGGER [Trigger_Exit'+@TableYearMonth+@StationID+'_Insert] ON [dbo].[Exit'+@TableYearMonth+@StationID+']'+Char(13)+'FOR INSERT '+Char(13)+
          'AS '+Char(13)+
            'BEGIN '+Char(13)+
                                     'Declare @Lane Tinyint,'+Char(13)+
           '            @Operator Int,'+Char(13)+
           '            @PassTime     DateTime,'+Char(13)+
           '            @OldPassTime  DateTime,'+Char(13)+
           '            @DealStatus   Smallint,'+Char(13)+
           '            @RecordType   Tinyint,'+Char(13)+
           '            @TotalCars    Tinyint,'+Char(13)+
           '            @Cash     Tinyint,'+Char(13)+
           '            @Unpaid   Tinyint,'+Char(13)+
           '            @Office   Tinyint,'+Char(13)+
                                      '            @ArmyPolice   Tinyint,'+Char(13)+
           '            @Emer     Tinyint,'+Char(13)+
           '            @FreePay  Tinyint,'+Char(13)+
           '            @Team     Tinyint,'+Char(13)+
           '            @Unmeet   Tinyint,'+Char(13)+
           '            @NoCard   Tinyint,'+Char(13)+
           '            @BadCard  Tinyint,'+Char(13)+
           '            @UTurn    Tinyint,'+Char(13)+
           '            @OutTime  Tinyint,'+Char(13)+
           '            @PathBreak    Tinyint,'+Char(13)+
                                      '            @BlackList    Tinyint,'+Char(13)+
           '            @GrayList     Tinyint,'+Char(13)+
           '            @Correct  Tinyint,'+Char(13)+
           '            @UnmeetCarNum Tinyint,'+Char(13)+
           '            @Simulate Tinyint,'+Char(13)+
           '            @UFree    Tinyint,'+Char(13)+
           '            @Fine     Tinyint'+Char(13)+Char(13)+

Answer 2

'            Select @Lane=ExitLane,@Operator=ExitOperator,@PassTime=ExitTime,@DealStatus=DealStatus,@RecordType=RecordType,'+Char(13)+ 
           '                       @TotalCars=0,@Cash=0,@Unpaid=0,@Office=0,@ArmyPolice=0,@Emer=0,@FreePay=0,@Team=0,@Unmeet=0,@NoCard=0,@BadCard=0,'+Char(13)+ 
           '                       @UTurn=0,@OutTime=0,@PathBreak=0,@BlackList=0,@GrayList=0,@Correct=0,@UnmeetCarNum=0,@Simulate=0,@UFree=0,@Fine=0'+Char(13)+ 
           '                       From INSERTED'+Char(13)+ 
           '            Select @OldPassTime=PassTime From Liveshow Where Lane=@Lane'+Char(13)+ Char(13)+
                                      '            /*TotalCars(Is Traffic and Not Fine)*/'+Char(13)+ 
           '            If (@RecordType & 0x1)=0x1 AND (@DealStatus & 0x1)=0'+Char(13)+ 
           '               Select @TotalCars=@TotalCars+1'+Char(13)+ Char(13)+ 
           '            /*Cash Car*/'+Char(13)+ 
           '            If (@DealStatus & 0xFFFF)=0'+Char(13)+ 
           '               Select @Cash=@Cash+1'+Char(13)+ Char(13)+
                                      '            /*Unpaid Car*/'+Char(13)+ 
           '            If (@DealStatus & 0xF)=0x4'+Char(13)+ 
           '               Select @Unpaid=@Unpaid+1'+Char(13)+ Char(13)+ 
           '            /*Office Car*/'+Char(13)+ 
           '            If (@DealStatus & 0xF)=0x6'+Char(13)+ 
           '               Select @Office=@Office+1'+Char(13)+ Char(13)+
                                      '            /*ArmyPolice Car*/'+Char(13)+ 
           '            If (@DealStatus & 0xF)=0x8'+Char(13)+ 
           '               Select @ArmyPolice=@ArmyPolice+1'+Char(13)+ Char(13)+
           '            /*Emer Car*/'+Char(13)+ 
           '            If (@DealStatus & 0xF)=0xA'+Char(13)+ 
           '               Select @Emer=@Emer+1'+Char(13)+ Char(13)+
                                      '            /*FreePay Car*/'+Char(13)+ 
           '            If (@DealStatus & 0xF)=0xC'+Char(13)+ 
           '               Select @FreePay=@FreePay+1'+Char(13)+ Char(13)+ 
           '            /*Team Car*/'+Char(13)+ 
           '            If (@DealStatus & 0xF)=0xE'+Char(13)+ 
           '               Select @Team=@Team+1'+Char(13)+ Char(13)+
                                      '            /*Unmeet Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x10)=0x10 '+Char(13)+ 
           '               Select @Unmeet=@Unmeet+1'+Char(13)+ Char(13)+ 
           '            /*NoCard Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x20)=0x20 '+Char(13)+ 
           '               Select @NoCard=@NoCard+1'+Char(13)+ Char(13)+
                                      '            /*UTurn Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x40)=0x40'+Char(13)+ 
               '               Select @UTurn=@UTurn+1'+Char(13)+ Char(13)+ 
           '            /*OutTime Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x80)=0x80 '+Char(13)+ 
           '               Select @OutTime=@OutTime+1'+Char(13)+ Char(13)+
                                      '            /*PathBreak Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x100)=0x100 '+Char(13)+ 
           '               Select @PathBreak=@PathBreak+1'+Char(13)+ Char(13)+ 
           '            /*BlackList Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x200)=0x200'+Char(13)+ 
           '               Select @BlackList=@BlackList+1'+Char(13)+ Char(13)+
                                      '            /*GrayList Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x400)=0x400'+Char(13)+ 
           '               Select @GrayList=@GrayList+1'+Char(13)+ Char(13)+ 
           '            /*BadCard Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x800)=0x800'+Char(13)+ 
           '               Select @BadCard=@BadCard+1'+Char(13)+ Char(13)+
                                      '            /*Correct Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x1000)=0x1000'+Char(13)+ 
           '               Select @Correct=@Correct+1'+Char(13)+ Char(13)+ 
           '            /*UnmeetCarNum Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x2000)=0x2000'+Char(13)+ 
           '               Select @UnmeetCarNum=@UnmeetCarNum+1'+Char(13)+ Char(13)+
                                      '            /*Simulate Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x4000)=0x4000'+Char(13)+ 
           '               Select @Simulate=@Simulate+1'+Char(13)+ Char(13)+ 
           '            /*UFree Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x8000)=0x8000'+Char(13)+ 
           '               Select @UFree=@UFree+1'+Char(13)+ Char(13)+
                                      '            /*Fine Car*/'+Char(13)+ 
           '            If (@DealStatus & 0x1)=0x1'+Char(13)+ 
           '               Select @Fine=@Fine+1'+Char(13)+ Char(13)+ 
           '            /*Update Liveshow*/'+Char(13)+ 
           '            If DateDiff(Second,@OldPassTime,@PassTime)>0'+Char(13)+ 

Answer 3

[code]
CREATE TRIGGER [table_aaa_config_on_insert] ON [table_aaa_config] 
FOR INSERT
ASDeclare @id int
Select @id = [id]
from inserted--添加config日志
Insert into table_aaa_config_log (keys,type,webshow,value,notes,createtime,feevalue)
Select keys,type,webshow,value,notes,createtime,feevalue
from table_aaa_config
where [id] = @idDeclare @keys varchar (20),
        @servicetype varchar (10),
        @feevalue varchar (6)Select @keys = keys,
       @servicetype = '1',
       @feevalue = feevalue
from table_aaa_config
where [id] = @idInsert Into sgip_fee (CmdCode,servicetype,feetype,feevalue,givenvalue,flag)
Values (@keys,@servicetype,'2',@feevalue,'0','0')
[/code]

Answer 4

create proc 过程名
@新表名 varchar(100)
as
begin
  exec('select * into '+@新表名+' from table_aaa')
  exec('
CREATE TRIGGER ['+@新表名+'_config_on_insert] ON ['+@新表名+']
FOR INSERT
ASDeclare @id int
Select @id = [id]
from inserted--添加config日志
Insert into table_aaa_config_log (keys,type,webshow,value,notes,createtime,feevalue)
Select keys,type,webshow,value,notes,createtime,feevalue
from table_aaa_config
where [id] = @idDeclare @keys varchar (20),
        @servicetype varchar (10),
        @feevalue varchar (6)Select @keys = keys,
       @servicetype = ''1'',
       @feevalue = feevalue
from table_aaa_config
where [id] = @idInsert Into sgip_fee (CmdCode,servicetype,feetype,feevalue,givenvalue,flag)
Values (@keys,@servicetype,''2'',@feevalue,''0'',''0'')
')
end

Answer 5

不管多长的Trigger我都讨厌！