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

--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<spa