一般企业网站存储过程锦集

--1根据ID,表名得到对应的信息
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetTitle]
(
  @ID nvarchar(20),
  @TableName nvarchar(30)
)
as
exec ('select Title from '+@TableName +' where ID='+@ID)
GO
--2插入新闻,产品信息
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[InsertProductInfo]
(
  @Title nvarchar(100),
  @ParentID int,
  @ModifyTime datetime,
  @Author nvarchar(30),
  @Hits int,
  @Content ntext,
  @PictureUrl nvarchar(100),
  @keyword nvarchar(100),
  @TableName nvarchar(50)
)
as
exec ('insert into '+@TableName+' (Title,ParentID,ModifyTime,Author,Hits,[Content],PictureUrl,Keyword)
values ('''+@Title+''','+@ParentID+','''+@ModifyTime+''','''+@Author+''','+@Hits+','''+@Content+''','''+@PictureUrl+''','''+@keyword+''')')
GO
--3根据标题,表名称得到ID信息
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetParentID]
(
  @Title nvarchar(30),
  @TableName nvarchar(30)
)
as
exec ('select ID from '+@TableName+' where Title='''+@Title+'''')
GO
--4
--6招聘信息
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[InsertCompanyEmployee]
(
  @Title nvarchar(50),
  @JobNum int,
  @JobPhone nvarchar(30),
  @JobExperience nvarchar(50),
  @Description nvarchar(300),
  @JobEducation nvarchar(50),
  @JobSalary nvarchar(50),
  @TableName nvarchar(50)
)
as
exec ('insert into '+@TableName+' (Title,JobNum,JobPhone,JobExperience,Description,JobEducation,JobSalary)
values ('''+@Title+''','+@JobNum+','''+@JobPhone+''','''+@JobExperience+''','''+@Description+''','''+@JobEducation+''','''+@JobSalary+''')')
GO
--7
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[UpdateCompanyEmployee]
(
  @ID int,
  @Title nvarchar(50),
  @JobNum int,
  @JobPhone nvarchar(30),
  @JobExperience nvarchar(50),
  @Description nvarchar(300),
  @JobEducation nvarchar(50),
  @JobSalary nvarchar(50),
  @TableName nvarchar(50)
)
as
exec ('update '+@TableName+' set Title='''+@Title+''',JobNum='+@JobNum+',JobPhone='''+@JobPhone+''',
JobExperience='''+@JobExperience+''',Description='''+@Description+''',JobEducation='''+@JobEducation+''',JobSalary='''+@JobSalary+'''where ID='+@ID)
GO
--8批量删除ID集合信息
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[DelAllByKeys]
(
@IDS nvarchar(100),
@TableName nvarchar(30)
)
as
exec('delete from '+@TableName +' where ID in'+'('+@IDS+')')
GO
--9根据删除
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[DelDataInfoByID]
(
  @ID nvarchar(20),
  @TableName nvarchar(30)
)
as
exec('delete from '+@TableName +' where ID='+@ID)
GO
--10
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[InsertLinkInfo]
(
  @Title nvarchar(50),
  @LinkUrl nvarchar(100),
  @PictureUrl nvarchar(100),
  @TableName nvarchar(50)
)
as
exec ('insert into '+@TableName+' (Title,LinkUrl,PictureUrl)
values ('''+@Title+''','''+@LinkUrl+''','''+@PictureUrl+''')')
GO
--11
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[InsertMessage]
(
  @Title nvarchar(50),
  @UserName nvarchar(30),
  @EMail nvarchar(50),
  @Phone nvarchar(20),
  @Address nvarchar(50),
  @Description nvarchar(500),
  @TableName nvarchar(50)
)
as
exec ('insert into '+@TableName+' (Title,UserName,EMail,Phone,Address,Description)
values('''+@Title+''','''+@UserName+''','''+@EMail+''','''+@Phone+''','''+@Address+''',
'''+@Description+''')')
GO
--12插入新闻，产品分类
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[InsertCategory]
(
  @Title nvarchar(50),
  @TableName nvarchar(50),
  @Keyword nvarchar(50),
  @ParentID nvarchar(50),
  @Depth int,
  @version nvarchar(20)
)
as
exec ('insert into '+@TableName+' (Title,Keyword,ParentID,Depth,Version)
values ('''+@Title+''','''+@Keyword+''','+@ParentID+','+@Depth+','''+@version+''')')
GO
--13批量更新表是否最新
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateAllByKeys]
(
   @IDS nvarchar(100),
   @TableName nvarchar(30)
)
as
declare @sql nvarchar(300)
set @sql='update '+@TableName+' set IsNew=(case when IsNew=1 then 0 else 1 end) where ID in'+'('+@IDS+')'
exec (@sql)
GO
--14
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateHits]
(
  @ID int,
  @TableName nvarchar(30),
  @Hits int
)
as
exec ('update '+@TableName+' set Hits='+@Hits+' where ID='+@ID)
GO
--15
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateLinkInfo]
(
  @ID int,
  @Title nvarchar(50),
  @LinkUrl nvarchar(100),
  @PictureUrl nvarchar(100),
  @TableName nvarchar(50)
)
as
exec ('update '+@TableName+' set Title='''+@Title+''',
        LinkUrl='''+@LinkUrl+''',PictureUrl='''+@PictureUrl+''' where ID='+@ID+'')
GO
--16
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateMessage]
(
  @ID int,
  @Reply nvarchar(500),
  @TableName nvarchar(50)
)
as
exec ('update '+@TableName+' set Reply='''+@Reply+''' where ID='+@ID+'')
GO
--17
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateNewsByTableName]
(
  @ID int,
  @Title nvarchar(50),
  @TableName nvarchar(50),
  @Keyword nvarchar(50),
  @ParentID nvarchar(50),
  @Depth int,
  @version nvarchar(20)
)
as
declare @sql nvarchar(300)
set @sql='update '+@TableName+' set Title='''+@Title+''',Keyword='''+@Keyword+''',ParentID='+@ParentID+',Depth='+@Depth+',version='''+@version+''' where ID='+@ID
exec (@sql)
GO
--18
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateProductInfoByID]
(
   @ID int,
   @Title nvarchar(50),
   @ModifyTime datetime,
   @Author nvarchar(50),
   @Hits int,
   @ParentID nvarchar(50),
   @Content ntext,
   @PictureUrl nvarchar(100),
   @Keyword nvarchar(100),
   @TableName nvarchar(50)
)
as
exec ('update '+@TableName+' set Title='''+@Title+''',Author='''+@Author+''',PictureUrl='''+@PictureUrl+'''
      ,ParentID='+@ParentID+',ModifyTime='''+@ModifyTime+''',Content='''+@Content+''',Hits='+@Hits+',Keyword='''+@Keyword+''' where ID='+@ID
     )
GO
--19
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetDepth]
(
  @TableName nvarchar(30),
  @ParentID int
)
as
exec ('select Depth from '+@TableName+' where ID='+@ParentID)
GO
--20
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetChildDepth]
(
  @ID int,
  @Depth int,
  @TableName nvarchar(50)
)
as
declare @res int
set @res=@Depth+1
if @res>3
begin
return 0
end
else
begin
exec ('update '+@TableName+' set Depth='+@res+'where ID='+@ID)
end
GO
--21
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  proc [dbo].[GetInfoByVersion]
(
  @TableName nvarchar(50),
  @Nums int,
  @IsNew nvarchar(20),
  @Version nvarchar(20),
  @ParentID nvarchar(20)
)
as
exec ('select top '+@Nums+' * from '+@TableName+' where version='''+@Version+''' and IsNew='+@IsNew+' and ParentID='+@ParentID+' Order by alterTime Desc')
GO
--22
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetParentTitleByID]
(
  @ID int,
  @TableName nvarchar(30)
)
as
exec ('select title from '+@TableName+' where id='+@ID)
GO
--23
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [qianhe].[InsertDownInfo]
(
  @Title nvarchar(50),
  @FileUrl nvarchar(200),
  @Description nvarchar(50),
  @Version nvarchar(50),
  @TableName nvarchar(50)
)
as
exec ('insert into '+@TableName+' (Title,FileUrl,Description,Version)
values ('''+@Title+''','''+@FileUrl+''','''+@Description+''','''+@Version+''')')
GO
/****** 对象:  StoredProcedure [qianhe].[UpDownInfoByID]    脚本日期: 02/06/2010 11:36:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [qianhe].[UpDownInfoByID]
(
  @Title nvarchar(50),
  @FileUrl nvarchar(200),
  @Description nvarchar(50),
  @ID int,
  @TableName nvarchar(50)
)
as
exec ('update '+@TableName+' set Title='''+@Title+''',FileUrl='''+@FileUrl+'''
,Description='''+@Description+''' where ID='+@ID
)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--24根据表名获得表信息
CREATE proc [dbo].[GetTableInfoByTableName]
(
  @TableName nvarchar(50),
  @Version nvarchar(20)
)
as
exec('select * from '+@TableName+' where version='''+@Version+'''')
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--25根据标题表名,标题得到对应的信息
CREATE proc [dbo].[GetDataByTitle]
(
  @Title nvarchar(50),
  @TableName nvarchar(50)
)
as
exec ('select * from '+@TableName+' where Title like ''%'+@Title+'%''')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--26根据表名,ID,得到对应ID信息
CREATE proc [dbo].[GetDataInfoByID]
(
  @ID nvarchar(20),
  @TableName nvarchar(30)
)
as
exec('select * from '+@TableName +' where ID='+@ID)
GO
/****** 对象:  StoredProcedure [dbo].[GetDataInfoByParentID]    脚本日期: 02/06/2010 11:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--27根据父类ID得到对应父分类的信息(主要用于新闻,产品)
CREATE proc [dbo].[GetDataInfoByParentID]
(
  @ParentID nvarchar(30),
  @TableName nvarchar(30)
)
as
exec('select * from '+@TableName +' where ParentID='+@ParentID+' order by ModifyTime DESC')
GO
--28
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetTableInfoByClass]  
(  
  @Num nvarchar(20),
  @Key nvarchar(20),
  @TableName nvarchar(30)  
)  
as  
declare @s nvarchar(200)   
select @s = isnull(@s+',','')+'['+name +']' from syscolumns where id  = object_id(@TableName) 
if @Num='1'
begin
exec('select top 1 '+@s+' from '+@TableName)
end
else
begin
if @Key='All'
begin
exec('select * from '+@TableName +' order by alterTime asc')
end
else
exec('select '+@s+' from '+@TableName+' where Keyword='''+@Key+''' order by alterTime asc')
end
GO
--29
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetTableInfoByOption]
(
  @TableName nvarchar(30),
  @Option nvarchar(30),
  @Num nvarchar(20),
  @IsNew nvarchar(2),
  @Keyword nvarchar(20)
)
as
exec('select top '+@Num+' * from '+@TableName+' where ParentID='+@Keyword+' and IsNew='+@IsNew+' order by ModifyTime desc')
GO
--30
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateInfo]
(
  @ID int,
  @Title nvarchar(50),
  @Keyword nvarchar(200),
  @Description nvarchar(300),
  @Content nvarchar(300),
  @Website nvarchar(50)
)
as
update qianheInfo set Title=@Title,keyword=@Keyword,Website=@Website,Description=@Description,
[Content]=@Content where ID=@ID
GO
--31
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetChildNumAndPic]
as
select P.ID,PID.cnt,P.PictureUrl,PID.Title from
(
select count(W.ID) cnt,P.Title,W.ParentID
from qianheProduct W,qianheProductcategory P where P.ID=W.ParentID group by P.Title,W.ParentID
)
PID
join
(
select min(PictureUrl) PictureUrl,min(ID) ID,ParentID from qianheProduct group by ParentID
)
P on PID.ParentID=P.ParentID
GO
--32
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdateHTMLByID]
(
  @ID int,
  @Hits int,
  @Content ntext
)
as
update qianheHTMLPage set Hits=@Hits,[Content]=@Content where ID=@ID
GO
--33
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UpdatePassword]
(
  @Title nvarchar(50),
  @Password nvarchar(100)
)
as
update qianheAdmin set Password=@Password where Title=@Title
GO
--34
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetAdminByLogin]
(
  @Title nvarchar(100),
  @Password nvarchar(100)
)
as
select count(*) from qianheAdmin where Title=@Title and Password=@Password
GO
--35
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetDataByParentID]
(
  @ID int
)
as
select * from qianheNewscategory where keyword in (select keyword from qianheNewscategory where ID=@ID)
GO