sql 存储过程的学习 和实例

CREATE      procedure createBookmarkReport
as

begin	
	declare @date nvarchar(8)
	declare @table nvarchar(2000)

	set @date=convert(nvarchar,dateadd(dd,-1,getdate()),112)	
	set @table = 'wapbase.wap_bookmark_' + convert(nvarchar,year(getdate()))

	--统计格式
	--序列号、日期、访问人数、访问次数、书签ID、书签名称、书签所有者	
	if not exists (select * from dbo.sysobjects where id = object_id( @table ) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
		begin
			exec (
				'create table ' + @table + '(
					seqid int not null identity(1,1) primary key, 
					id int not null,
					title nvarchar(50) not null, 					
					own nvarchar(50),
					views int not null default(0),
					visits int not null default(0),
					[date] nvarchar(8) not null
				)'
			)
		end

	if year(@date) < year(getdate())
		set @table = 'wapbase.wap_bookmark_' + convert(nvarchar,year(@date))

	declare @prefix nvarchar(100)
	declare @subfix nvarchar(100)
	declare @sql nvarchar(2000)

	set @prefix = 'http://wap.lenovomobile.com/bookmark.do?id='
	set @subfix = '&'

	set @sql = '
	insert into ' + @table + '(id,title,own,views,visits,[date]) 		
	select b.seqid, b.title, b.own, count(*) views, count(distinct mid) visits,''' + @date + ''' 
	from wap_lenovo_bookmark b left join (
		select substring(url,len(''' + @prefix + ''')+1,len(url)-len(''' + @prefix +''')) id, ua, mid
	'
	set @sql = @sql + ' from wapbase.wap_log_' + @date
		+ ' where charindex(''' + @prefix + ''',url,1)>0  and  charindex(''' + @subfix + ''',url,1) = 0'

		+ ' union all '

		+ ' select substring(url,len(''' + @prefix + ''')+1,charindex(''' + @subfix + ''',url,1)-len(''' + @prefix + ''')-1) id, ua, mid'
		+ ' from wapbase.wap_log_' + @date
		+ ' where charindex(''' + @prefix + ''',url,1)>0  and  charindex(''' + @subfix + ''',url,1) > 0'			
	+ ') a  on b.seqid = a.id'
	+ ' group by b.seqid, b.title, b.own'
	+ ' order by b.seqid desc'

	exec(@sql)


end


GO