--测试数据表 if object_id('u_scout_info') is not null drop table u_scout_info go create table u_scout_info (ID int,Org_id int , Logo_id int ,ad_id int,Ad_form_id int ) if object_id('u_scout_date') is not null drop table u_scout_date go create table u_scout_date(ID int identity(1, 1) , Scout_ID int,[Date] varchar(10),[Time] varchar(12)) --定义存储过程 if object_id('insert_proc') is not null drop proc insert_proc go create proc insert_proc (@id int, @org_id int, @logo_id int, @ad_id int, @ad_form_id int) as declare @count int, @date varchar(10), @time varchar(12) set @date=convert(varchar(4), datepart(yy, getdate()))+'-' +convert(varchar(2), datepart(mm, getdate()))+'-' +convert(varchar(2), datepart(dd, getdate())) set @time=convert(varchar(2), datepart(hh, getdate()))+':' +convert(varchar(2), datepart(mm, getdate()))+':' +convert(varchar(2), datepart(ss, getdate()))+':' +convert(varchar(3), datepart(ms, getdate())) --select convert(datetime, @date+' '+@time) select @count=count(*) from u_scout_info where id=@id and org_id=@org_id and logo_id=@logo_id and ad_id=@ad_id and ad_form_id=@ad_form_id
if @count=0 insert u_scout_info select @id, @org_id, @logo_id, @ad_id, @ad_form_id insert u_scout_date(scout_id, [date], [time]) select @id, @date, @time go --测试示例 exec insert_proc 1, 1, 2, 3, 4 exec insert_proc 1, 1, 2, 3, 4 select* from u_scout_info select* from u_scout_date --扫尾 drop table u_scout_info drop table u_scout_date
--测试数据表 if object_id('u_scout_info') is not null drop table u_scout_info go create table u_scout_info (ID int identity(1, 1),Org_id int , Logo_id int ,ad_id int,Ad_form_id int ) if object_id('u_scout_date') is not null drop table u_scout_date go create table u_scout_date(ID int identity(1, 1) , Scout_ID int,[Date] varchar(10),[Time] varchar(12)) --定义存储过程 if object_id('insert_proc') is not null drop proc insert_proc go create proc insert_proc (@id int, @org_id int, @logo_id int, @ad_id int, @ad_form_id int) as declare @count int, @date varchar(10), @time varchar(12) set @date=convert(varchar(4), datepart(yy, getdate()))+'-' +convert(varchar(2), datepart(mm, getdate()))+'-' +convert(varchar(2), datepart(dd, getdate())) set @time=convert(varchar(2), datepart(hh, getdate()))+':' +convert(varchar(2), datepart(mm, getdate()))+':' +convert(varchar(2), datepart(ss, getdate()))+':' +convert(varchar(3), datepart(ms, getdate())) --select convert(datetime, @date+' '+@time) select @count=count(*) from u_scout_info where id=@id and org_id=@org_id and logo_id=@logo_id and ad_id=@ad_id and ad_form_id=@ad_form_id
if @count=0 insert u_scout_info(org_id, logo_id, ad_id, ad_form_id) select @org_id, @logo_id, @ad_id, @ad_form_id insert u_scout_date(scout_id, [date], [time]) select @id, @date, @time go --测试示例 exec insert_proc 1, 1, 2, 3, 4 exec insert_proc 1, 1, 2, 3, 4 select* from u_scout_info select* from u_scout_date --扫尾 drop table u_scout_info drop table u_scout_date
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[u_scout_info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[u_scout_info] GOCREATE TABLE [dbo].[u_scout_info] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Org_id] [int] NULL , [Logo_id] [int] NULL , [ad_id] [int] NULL , [Ad_form_id] [int] NULL ) ON [PRIMARY] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[u_scout_date]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[u_scout_date] GOCREATE TABLE [dbo].[u_scout_date] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Scout_ID] [int] NULL , [Date] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [Time] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO--存储过程 create procedure temppro @Org_id varchar(300)=null, @Logo_id varchar(300)=null, @ad_id varchar(300)=null, @Ad_form_id varchar(300)=null as
if (@Org_id is not null) and (@Logo_id is not null) and (@ad_id is not null) and (@Ad_form_id is not null) begin
if exists(select 1 from u_scout_info where org_id=@org_id and logo_id=@logo_id and ad_id=@ad_id and ad_form_id=@ad_form_id ) begin insert into u_scout_date (Scout_ID,[date],[Time]) select id,convert(varchar,getdate(),110),convert(varchar,getdate(),108) from u_scout_info where org_id=@org_id and logo_id=@logo_id and ad_id=@ad_id and ad_form_id=@ad_form_id end else begin insert into u_scout_info(org_id,logo_id,ad_id,ad_form_id) select @org_id, @logo_id, @ad_id, @ad_form_id insert into u_scout_date(Scout_ID,[date],[Time]) select id,convert(varchar,getdate(),110),convert(varchar,getdate(),108) from u_scout_info where org_id=@org_id and logo_id=@logo_id and ad_id=@ad_id and ad_form_id=@ad_form_id end end else print '需要4个参数,检查参数是否正确!'--测试数据temppro '2','3','4','6'select * from u_scout_infoselect * from u_scout_date --删除表--drop table u_scout_info--drop table u_scout_date
if object_id('ta') is not null drop table ta go create table ta(id int, value int) insert ta select 1, 2 union all select 1, 3 union all select 2, 2 --示例 declare @value int select @value=value from ta where id=2 select @value
if object_id('u_scout_info') is not null drop table u_scout_info
go
create table u_scout_info (ID int,Org_id int ,
Logo_id int ,ad_id int,Ad_form_id int )
if object_id('u_scout_date') is not null drop table u_scout_date
go
create table u_scout_date(ID int identity(1, 1) ,
Scout_ID int,[Date] varchar(10),[Time] varchar(12))
--定义存储过程
if object_id('insert_proc') is not null drop proc insert_proc
go
create proc insert_proc (@id int, @org_id int, @logo_id int,
@ad_id int, @ad_form_id int)
as
declare @count int, @date varchar(10), @time varchar(12)
set @date=convert(varchar(4), datepart(yy, getdate()))+'-'
+convert(varchar(2), datepart(mm, getdate()))+'-'
+convert(varchar(2), datepart(dd, getdate()))
set @time=convert(varchar(2), datepart(hh, getdate()))+':'
+convert(varchar(2), datepart(mm, getdate()))+':'
+convert(varchar(2), datepart(ss, getdate()))+':'
+convert(varchar(3), datepart(ms, getdate()))
--select convert(datetime, @date+' '+@time) select @count=count(*) from u_scout_info
where id=@id and org_id=@org_id and logo_id=@logo_id
and ad_id=@ad_id and ad_form_id=@ad_form_id
if @count=0 insert u_scout_info
select @id, @org_id, @logo_id, @ad_id, @ad_form_id insert u_scout_date(scout_id, [date], [time])
select @id, @date, @time
go
--测试示例
exec insert_proc 1, 1, 2, 3, 4
exec insert_proc 1, 1, 2, 3, 4
select* from u_scout_info
select* from u_scout_date
--扫尾
drop table u_scout_info
drop table u_scout_date
if object_id('u_scout_info') is not null drop table u_scout_info
go
create table u_scout_info (ID int identity(1, 1),Org_id int ,
Logo_id int ,ad_id int,Ad_form_id int )
if object_id('u_scout_date') is not null drop table u_scout_date
go
create table u_scout_date(ID int identity(1, 1) ,
Scout_ID int,[Date] varchar(10),[Time] varchar(12))
--定义存储过程
if object_id('insert_proc') is not null drop proc insert_proc
go
create proc insert_proc (@id int, @org_id int, @logo_id int,
@ad_id int, @ad_form_id int)
as
declare @count int, @date varchar(10), @time varchar(12)
set @date=convert(varchar(4), datepart(yy, getdate()))+'-'
+convert(varchar(2), datepart(mm, getdate()))+'-'
+convert(varchar(2), datepart(dd, getdate()))
set @time=convert(varchar(2), datepart(hh, getdate()))+':'
+convert(varchar(2), datepart(mm, getdate()))+':'
+convert(varchar(2), datepart(ss, getdate()))+':'
+convert(varchar(3), datepart(ms, getdate()))
--select convert(datetime, @date+' '+@time) select @count=count(*) from u_scout_info
where id=@id and org_id=@org_id and logo_id=@logo_id
and ad_id=@ad_id and ad_form_id=@ad_form_id
if @count=0 insert u_scout_info(org_id, logo_id, ad_id, ad_form_id)
select @org_id, @logo_id, @ad_id, @ad_form_id insert u_scout_date(scout_id, [date], [time])
select @id, @date, @time
go
--测试示例
exec insert_proc 1, 1, 2, 3, 4
exec insert_proc 1, 1, 2, 3, 4
select* from u_scout_info
select* from u_scout_date
--扫尾
drop table u_scout_info
drop table u_scout_date
drop table [dbo].[u_scout_info]
GOCREATE TABLE [dbo].[u_scout_info] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Org_id] [int] NULL ,
[Logo_id] [int] NULL ,
[ad_id] [int] NULL ,
[Ad_form_id] [int] NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[u_scout_date]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[u_scout_date]
GOCREATE TABLE [dbo].[u_scout_date] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Scout_ID] [int] NULL ,
[Date] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Time] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO--存储过程
create procedure temppro @Org_id varchar(300)=null,
@Logo_id varchar(300)=null,
@ad_id varchar(300)=null,
@Ad_form_id varchar(300)=null
as
if (@Org_id is not null) and (@Logo_id is not null) and (@ad_id is not null) and (@Ad_form_id is not null)
begin
if exists(select 1 from u_scout_info where org_id=@org_id and logo_id=@logo_id
and ad_id=@ad_id and ad_form_id=@ad_form_id )
begin
insert into u_scout_date (Scout_ID,[date],[Time])
select id,convert(varchar,getdate(),110),convert(varchar,getdate(),108) from u_scout_info where org_id=@org_id and logo_id=@logo_id
and ad_id=@ad_id and ad_form_id=@ad_form_id
end
else
begin
insert into u_scout_info(org_id,logo_id,ad_id,ad_form_id)
select @org_id, @logo_id, @ad_id, @ad_form_id insert into u_scout_date(Scout_ID,[date],[Time])
select id,convert(varchar,getdate(),110),convert(varchar,getdate(),108) from u_scout_info where org_id=@org_id and logo_id=@logo_id
and ad_id=@ad_id and ad_form_id=@ad_form_id
end end
else
print '需要4个参数,检查参数是否正确!'--测试数据temppro '2','3','4','6'select * from u_scout_infoselect * from u_scout_date
--删除表--drop table u_scout_info--drop table u_scout_date
go
create table ta(id int, value int)
insert ta select 1, 2
union all select 1, 3
union all select 2, 2
--示例
declare @value int
select @value=value from ta where id=2
select @value