1.自动建表存储过程
use [threetoone]
go
/****** object: storedprocedure [dbo].[wto_createtable_scandoxxx] script date: 01/08/2019 15:20:09 ******/
set ansi_nulls on
go
set quoted_identifier on
go
— =============================================
— author: litiantian
— create date: 2018/07/12
— description:
— =============================================
alter procedure [dbo].[wto_createtable_scandoxxx]
— add the parameters for the stored procedure here
–<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
–<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
as
declare @year varchar(4)
declare @month varchar(4)
declare @target varchar(18)
declare @str varchar(8000)
set @year = datepart(yyyy,getdate())
set @month = datepart(week,getdate()) +1
set @month=replace(right(str(@month),4),’ ‘,’0’)
set @target= ‘scando_’ + @year + @month
if not exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[‘ + @target + ‘]’) and objectproperty(id, n’isusertable’) = 1)
begin
set @str = ‘
create table [dbo].[‘ + @target + ‘] (
[id] [int] identity(1,1) not null,
[facode] [varchar](50) not null,
[sncode] [varchar](25) null,
[bicode] [varchar](25) null,
[mdcode] [varchar](15) null,
[location] [varchar](50) null,
[carnum] [varchar](50) null,
[localnum] [varchar](50) null,
[flagfc] [varchar](50) null,
[flagcl] [varchar](50) null,
[userid] [varchar](10) null,
[logonmac] [varchar](50) null,
[workname] [varchar](20) null,
[writedate] [datetime] default (convert([varchar],getdate(),(120))) null,
[beforloca] [varchar](50) null,
) on [primary]
‘
exec (@str)
set @str = ‘alter table ‘ + @target + ‘ add primary key (id,facode)’
exec (@str)
end
2.配合代理作业定时执行存储过程