--22更新产品信息
create proc [dbo].[UpdateProductInfoByID]
(
@ID nvarchar(20),
@Title nvarchar(50),
@ModifyTime datetime,
@Author nvarchar(50),
@Hits int,
@ParentID nvarchar(50),
@Content ntext,
@PictureUrl nvarchar(100),
@Keyword nvarchar(100)
)
as
update shuimutingProduct set Title=@Title,Author=@Author,PictureUrl=@PictureUrl,ParentID=@ParentID,ModifyTime=@ModifyTime,Content=@Content,
Hits=@Hits,Keyword=@Keyword where ID=@ID
--23更新密码
create proc [dbo].[UpdatePassword]
(
@Title nvarchar(50),
@Password nvarchar(100)
)
as
update shuimutingAdmin set Password=@Password where Title=@Title
--24得到父类的Depth值
create proc [dbo].[GetDepth]
(
@TableName nvarchar(30),
@ParentID int
)
as
exec ('select Depth from '+@TableName+' where ID='+@ParentID)
--25根据子分类的ID,父分类的Depth更新子分类的depth值
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
--26中医文版(根据表名,数量,是否最新,版本,父类ID)
alter 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 CreateTime Desc')
--27根据父类ID得到分类名称
create proc [dbo].[GetParentTitleByID]
(
@ID int,
@TableName nvarchar(30)
)
as
exec ('select title from '+@TableName+' where id='+@ID)
--28下载信息
create proc [InsertDownInfo]
(
@Title nvarchar(50),
@FileUrl nvarchar(200),
@Description nvarchar(50),
@Version nvarchar(50)
)
as
insert into kailingDownload(Title,FileUrl,Description,Version)
select @Title,@FileUrl,@Description,@Version
--29修改下载信息列表
create proc UpDownInfoByID
(
@Title nvarchar(50),
@FileUrl nvarchar(200),
@Description nvarchar(50),
ID int
)
as
update kailingDownload set Title=@Title,FileUrl=@FileUrl,Description=@Description where ID=@ID
--30根据父分类ID得到该分类相同关键字的信息
create proc [dbo].[GetDataByParentID]
(
@ID int
)
as
select * from shuimutingNewscategory where keyword in (select keyword from shuimutingNewscategory where ID=@ID)
--31根据分类 ID得到该分类下的产品数量,第一张图片的，ID
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 shuimutingProduct W,shuimutingProductcategory P where P.ID=W.ParentID group by P.Title,W.ParentID
)
PID
join
(
select min(PictureUrl) PictureUrl,min(ID) ID,ParentID from shuimutingProduct group by ParentID
)
P on PID.ParentID=P.ParentID
--32根据ID,表明得到标题名称
create proc [dbo].[GetTitle]
(
@ID nvarchar(20),
@TableName nvarchar(30)
)
as
exec ('select Title from '+@TableName +' where ID='+@ID)
--33插入产品信息
create proc [dbo].[InsertProductInfo]
(
@Title nvarchar(100),
@ParentID int,
@ModifyTime datetime,
@Author nvarchar(30),
@Hits int,
@Content ntext,
@PictureUrl nvarchar(100),
@keyword nvarchar(100)
)
as
insert into shuimutingProduct (Title,ParentID,ModifyTime,Author,Hits,[Content],PictureUrl,Keyword)
values (@Title,@ParentID,@ModifyTime,@Author,@Hits,@Content,@PictureUrl,@keyword)
--34根据分类信息得到它的ID
create proc [dbo].[GetParentID]
(
@Title nvarchar(30),
@TableName nvarchar(30)
)
as
exec ('select ID from '+@TableName+' where Title='''+@Title+'''')
--35订单信息
create proc [dbo].[InsertOrder]
(
@Address nvarchar(50),
@Quality nvarchar(50),
@UserName nchar(2),
@Job nvarchar(50),
@Phone nvarchar(50),
@EMail nvarchar(50),
@Num int,
@House nvarchar(50),
@Category nvarchar(50),
@Area nvarchar(50),
@Fee nvarchar(50),
@HouseDate nvarchar(50),
@Style nvarchar(50),
@Description nvarchar(500)
)
as
insert into shuimutingOrder
(Address,Quality,UserName,Job,Phone,EMail,Num,House,Category,Area,Fee,HouseDate,Style,Description)
values
(@Address,@Quality,@UserName,@Job,@Phone,@EMail,@Num,@House,@Category,@Area,@Fee,@HouseDate,@Style,@Description)
--36修改表单信息
create proc [dbo].[UpdateOrder]
(
@ID int,
@Address nvarchar(50),
@Quality nvarchar(50),
@UserName nchar(2),
@Job nvarchar(50),
@Phone nvarchar(50),
@EMail nvarchar(50),
@Num int,
@House nvarchar(50),
@Category nvarchar(50),
@Area nvarchar(50),
@Fee nvarchar(50),
@HouseDate nvarchar(50),
@Style nvarchar(50),
@Description nvarchar(500)
)
as
update shuimutingOrder set Address=@Address,Quality=@Quality,UserName=@UserName,Job=@Job
,Phone=@Phone,EMail=@EMail,Num=@Num,House=@House,Category=@Category,Area=@Area,Fee=@Fee,HouseDate=@HouseDate,
Style=@Style,Description=@Description
--37应聘信息表
create proc [dbo].[InsertCompanyEmployee]
(
@Title nvarchar(50),
@JobNum int,
@JobPhone nvarchar(30),
@JobExperience nvarchar(50),
@Description nvarchar(300),
@JobEducation nvarchar(50),
@JobSalary nvarchar(50)
)
as
insert into yinquanCompanyEmployee (Title,JobNum,JobPhone,JobExperience,Description,JobEducation,JobSalary) values
(@Title,@JobNum,@JobPhone,@JobExperience,@Description,@JobEducation,@JobSalary)
--38修改应聘信息表
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)
)
as
update yinquanCompanyEmployee set Title=@Title,JobNum=@JobNum,JobPhone=@JobPhone,
JobExperience=@JobExperience,Description=@Description,JobEducation=@JobEducation,JobSalary=@JobSalary where ID=@ID
