存储过程收集_存储过程数据收集-CSDN博客

本文链接：https://blog.csdn.net/unciel/article/details/3978153

存储过程spCreateBoard：
//两张表表tbClass存放论坛分类表tbBoard存放论坛分类中的版块

CREATE PROCEDURE spCreateBoard

(

@ClassName varchar(50), //输入参数

@BoardName varchar(50), //输入参数

@ClassID varchar(50) output //输出参数

)

declare @BoardCount int;

set @ClassID=(select ClassID from tbClass where ClassName = @ClassName);

insert into tbBoard(BoardName,BoardClassID) values (@BoardName,@ClassID);

set @BoardCount = (select count(*) from tbBoard);

return @BoardCount;

界面TextBox1和TextBox2：

******************************************************

如果存储过程中包含有返回值的存储过程，那我们就必须指定参数值.看下面这个例子
此例摘自《SQLserver程序员指南》一书
create procedure salequa @stor_id char 4 ,@sum smallint output
as
select ord_num, ord_date, payterms, title_id, qty
from sales
where stor_id = @stor_id
select @sum = sum qty
from sales
where stor_id = @stor_id
go

要执行此存储过程，则我们要指定参数@sort_id，@sum的参数值.
declare @totalqua smallint
execute salequa '7131',@totalqua output
if @totalqua<=50
select '销售信息'='销售等级为3 销售量为'+rtrim cast @totalqua as varchar 20
if @totalqua>50 and @totalqua<=100
select '销售信息'='销售等级为2 销售量为'+rtrim cast @totalqua as varchar 20
if @totalqua>100
select '销售信息'='销售等级为1 销售量为'+rtrim cast @totalqua as varchar 20
运行结果为
ord_num ord_date payterms title_id qty
-------------------- --------------------------- ------------ -------- ------
N914008 1994-09-14 00:00:00.000 Net 30 PS2091 20
N914014 1994-09-14 00:00:00.000 Net 30 MC3021 25
P3087a 1993-05-29 00:00:00.000 Net 60 PS1372 20
P3087a 1993-05-29 00:00:00.000 Net 60 PS2106 25
P3087a 1993-05-29 00:00:00.000 Net 60 PS3333 15
P3087a 1993-05-29 00:00:00.000 Net 60 PS7777 25
6 row s affected
销售信息
-----------------------------------------
销售等级为1 销售量为130

(3)常用的一些存储过程例子
以下是自己在最近所做的一个项目中所用到的一些存储过程，可能由于自己水平有限，有些写得不是很规范，不过大部分都实现到我想要的结果了，这些存储过程都可以正常执行,把这些发出来给大家(数据库因保密请见谅)，希望对大家用用，同时希望指正其中的错误，谢谢。

(1) 选择所有的记录

create procedure sellinfo_select
as
select * from sellinfo
GO

2) 删除指定的ID记录

用途：删除sellinfo里由输入参数@sell_id指定的ID记录
日期：2006-3-23
*/
CREATE PROCEDURE sellinfo_delete
@sell_id bigint
as
delete from [sellinfo]
where
sell_id=@sell_id
GO

(3)更新所对应的记录

create procedure prosmallclass_update_id
@smallid int,
@smallname char(50)
as
update [ProductCats]
set
PdtCat_Name = @smallname
where
PdtCat_id =@smallid
GO
(4)验证登陆

CREATE procedure user_login
@user_name varchar(50),
@user_password varchar(50)
as
select * from usercompany where [User_Name] = @User_Name and [User_Pwd] = @User_Password
if @@rowcount>0
begin
update [users] set user_LoginTimes=user_LoginTimes+1 where [User_Name] = @User_Name and [User_Pwd] = @User_Password
end
GO
(5)密码修改

@iOutput int output
as
if exists(select * from users where User_Name=@user_name and user_pwd=@user_oldpwd)
begin
update users set user_pwd=@user_newpwd where User_Name=@user_name and user_pwd=@user_oldpwd
set @iOutput = 1
end
else
set @ioutput = -1
GO

(6)增加新记录

)
values
(
@gbusername,
@gbusermemberid,
@gbuseremail,
@gbusersubject,
@gbusercontent
)
GO
(7)统计数据

SupplyRcmd =@SupplyRcmd,
SupplyUnchk=@SupplyUnchk,
SupplyChk =@SupplyChk,
NewsCount=@NewsCount,
NewsRcmd=@NewsRcmd,
NewsClassCount=@NewsClassCount,
probigclass=@probigclass,
prosmallclass=@prosmallclass,
MsgCount = @MsgCount
GO

8)模糊查询

/*
作者：德仔
用途：用来进行查询sell_info
日期：2006-4-10
*/
CREATE PROCEDURE sellinfo_search
@keyword nvarchar (20)
AS
select sell_subject from sellinfo where sell_subject like '%' + @keyword + '%'
GO
******************************************************************
create procedure SelectProduceInfo
@Ope varchar(50),
@ProBeginDate datetime,
@ProEndDate datetime,
@IP varchar(50),
@ComputerName varchar(50),
@ProType varchar(50),
@DiskType varchar(50),
@KBID varchar(50),
@CustName varchar(50),
@CfgID int,
@ProBatch varchar(50)
as
select Operator as '操作员',ProduceDate as '生产日期',IPAddress as 'IP地址',ComputerName as '计算机名',
'产品类型'=case ProductType when '1' then 'Key' when '2' then 'U盘' when '3' then 'U盘/Key' end,
'U盘类型'=case UdiskType when '0' then '单机版安全U盘' when '1' then '企业版安全U盘' when '2' then '企业版管理盘' end,
KBUdiskID as 'U盘金邦ID',CustomerName as '客户',CfgName as '配置',
ProduceBatch as '生产批次号',ProduceRemark as '生产备注' from ProduceUdiskKeyInfo a inner join UdiskPolicyConfigInfo b
on a.ConfigAutoID=b.ConfigAutoID
where Operator=isnull(@Ope,Operator) and ProduceDate>=isnull(@ProBeginDate,ProduceDate)
and ProduceDate<=isnull(@ProEndDate,ProduceDate) and IPAddress=isnull(@IP,IPAddress)
and ComputerName=isnull(@ComputerName,ComputerName) and ProductType=isnull(@ProType,ProductType)
and UdiskType=isnull(@DiskType,UdiskType) and KBUdiskID=isnull(@KBID,KBUdiskID)
and CustomerName=isnull(@CustName,CustomerName) and a.ConfigAutoID=isnull(@CfgID,a.ConfigAutoID)
and ProduceBatch=isnull(@ProBatch,ProduceBatch)
go
**************************************
create PROCEDURE dbo.WallpaperContentMenu_Get
(
@inAppID int,
@inDateTime datetime = null,
@isStageMenu bit = 0
)
......

declare @theMenuAppID int
select @theMenuAppID = isNull(cMenuAppID, @inAppID)
from dbo.TB_App with (NOLOCK)
where cAppID = @inAppID

isNull(cMenuAppID,@inAppID)
表示
if cMenuAppID = null then
@theMenuAppID = @inAppID
else
@theMenuAppID = cMenuAppID

**************************************************
when   a   row   is   inserted   in   table   TZ*/
CREATE   TRIGGER   Ztrig
ON   TZ
FOR   INSERT   AS
        BEGIN
        INSERT   TY   VALUES   ('')
        END

/*FIRE   the   trigger   and   find   out   what   identity   values   you   get
with   the   @@IDENTITY   and   SCOPE_IDENTITY   functions*/
INSERT   TZ   VALUES   ('Rosalie')

SELECT   SCOPE_IDENTITY()   AS   [SCOPE_IDENTITY]
GO
SELECT       @@IDENTITY   AS   [@@IDENTITY]
GO

--Here   is   the   result   set.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY   returned   the   last   identity   value   in   the   same   scope,   which   was   the   insert   on   table   TZ*/

@@IDENTITY
115
/*@@IDENTITY   returned   the   last   identity   value   inserted   to   TY   by   the   trigger,   which   fired   due   to   an   earlier   insert   on   TZ*/

**********************************************
Create Trigger truStudent
      On Student                        --在Student表中创建触发器
      for Update                         --为什么事件触发
    As                                       --事件触发后所要做的事情
      if Update(StudentID)
      begin

        Update BorrowRecord
          Set StudentID=i.StudentID
          From BorrowRecord br , Deleted d ,Inserted i     --Deleted和Inserted临时表
          Where br.StudentID=d.StudentID

      end

    理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。
    一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：
                            虚拟表Inserted                    虚拟表Deleted

****************************************