代码语言
.
CSharp
.
JS
Java
Asp.Net
C
MSSQL
PHP
Css
PLSQL
Python
Shell
EBS
ASP
Perl
ObjC
VB.Net
VBS
MYSQL
GO
Delphi
AS
DB2
Domino
Rails
ActionScript
Scala
代码分类
文件
系统
字符串
数据库
网络相关
图形/GUI
多媒体
算法
游戏
Jquery
Extjs
Android
HTML5
菜单
网页交互
WinForm
控件
企业应用
安全与加密
脚本/批处理
开放平台
其它
【
MSSQL
】
一般企业网站存储过程锦集
作者:
/ 发布于
2016/4/26
/
549
--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
试试其它关键字
企业网站
存储过程
同语言下
.
SQL查询 多列合并成一行用逗号隔开
.
查看存储过程修改时间,最近执行时间
.
设置手动批量删除数据库相关进程
.
获取某个表中特定字段的所有字符串形式
.
SQL 如何去除重复的字符串
.
怎么去掉一个字段中的重复数据
.
String 去除空格 回车 换行 水平制表符
.
SQL查询和替换含有回车,空格,TAB
.
SQL SERVER 查询每日新增用户数量、次留数量
.
判断两个字符串是否存在相同的内容
可能有用的
.
C#实现的html内容截取
.
List 切割成几份 工具类
.
SQL查询 多列合并成一行用逗号隔开
.
一行一行读取txt的内容
.
C#动态修改文件夹名称(FSO实现,不移动文件)
.
c# 移动文件或文件夹
.
c#图片添加水印
.
Java PDF转换成图片并输出给前台展示
.
网站后台修改图片尺寸代码
.
处理大图片在缩略图时的展示
贡献的其它代码
Label
Copyright © 2004 - 2024 dezai.cn. All Rights Reserved
站长博客
粤ICP备13059550号-3