论坛交流
首页办公自动化| 网页制作| 平面设计| 动画制作| 数据库开发| 程序设计| 全部视频教程
应用视频: Windows | Word2007 | Excel2007 | PowerPoint2007 | Dreamweaver 8 | Fireworks 8 | Flash 8 | Photoshop cs | CorelDraw 12
编程视频: C语言视频教程 | HTML | Div+Css布局 | Javascript | Access数据库 | Asp | Sql Server数据库Asp.net  | Flash AS
当前位置 > 文字教程 > Sql Server教程
Tag:注入,存储过程,分页,安全,优化,加密,索引,日志,压缩,base64,函数,内存,PDF,迁移,结构,破解,编译,配置,进程,分词,触发器,socket,安装,sqlserver2000,sqlserver2005,sqlserver2008,视频教程

存储过程使用技巧

文章类别:Sql Server | 发表日期:2008-10-5 21:37:40

================以下是存储过程================
/*
加入一条基本信息
库存商品

-1 inttostr 失败
-2 有相关数据存在
-3 parid 不存在
-4 记录已存在
*/

CREATE PROCEDURE F_B_InsertP
(@Parid varchar(25),
@dbname varchar(30),
@FullName varchar(66),
@Namevarchar(30),
@UserCode varchar(26),
@Standard varchar(120),
@Type varchar(40),
@Area varchar(30),
@Unit1 varchar(8),
@Unit2 varchar(8),
@UnitRate1 numeric(18,4),
@UnitRate2 numeric(18,4),
@preprice1 numeric(18,4),
@preprice2 numeric(18,4),
@LifeMonth int,
@LifeDay int,
@Comment varchar(256),
@namepyvarchar(60),
@Barcodevarchar(30)
)
AS
Declare @nReturntype int
Declare @EtypeId_1 varchar(25)
Declare @nSoncount int
Declare @nSonnum int
Declare @leveal smallint
Declare @ParRec int
Declare @checkValue int

-- 取得ID号

exec @nReturntype=F_B_createId @ParId,@dbname,@EtypeId_1 out,@nSonnum out,@nSoncount out,

@ParRec out

if @nReturntype=-101 goto error111
if @nReturntype=-102 goto error112
if @nReturntype=-103 goto error113


Exec @checkValue=mzw_CheckBasicSet
if @checkValue=1 -- 查找编号和全名不能完全相同
begin
if exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or ([fullname]=@fullname
and [usercode]=@usercode) and [deleted]<>1) goto error114
end
if @checkValue=2--全名不能完全相同
begin
if exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or ([fullname]=@fullname
) and [deleted]<>1) goto error114
end
if @checkValue=3--编号不能完全相同
begin
if exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or (
[usercode]=@usercode) and [deleted]<>1) goto error114
end
if @checkValue=4 -- 查找编号和全名不能完全相同
begin
if exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or ([fullname]=@fullname
or [usercode]=@usercode) and [deleted]<>1) goto error114
end
if (len(@Barcode)>0)
begin
if exists(Select * From ptype
Where ([Barcode]=@Barcode) and [deleted]<>1) return -5
end

BEGIN TRAN InsertP

Select @leveal=[leveal] From ptype Where [typeid]=@Parid
Select @leveal=@leveal+1

Insert into [ptype]
([typeId], [Parid], [leveal], [soncount], [sonnum],
[FullName], [Name], [UserCode], [Standard], [Type],
[Area], [Unit1], [Unit2], [UnitRate1], [UnitRate2],
[preprice1], [preprice2], [UsefulLifeMonth],
[UsefulLifeDay], [Comment], [namepy], [parrec],
[Barcode])

values (@EtypeId_1, @Parid, @leveal, 0, 0,
@FullName, @Name, @UserCode, @Standard, @Type,
@Area, @Unit1, @Unit2, @UnitRate1, @UnitRate2,
@preprice1, @preprice2, @LifeMonth,
@LifeDay, @Comment, @namepy, @parrec,
@Barcode)

if @@rowcount=0
begin
ROLLBACK TRAN InsertP
Return -1
end
else
begin
Update [ptype]
set [sonnum]=@nSonnum+1,[soncount]=@nSoncount+1
Where [typeid]=@Parid
end

COMMIT TRAN InsertP

goto succee

succee:
Return 0

error111:
Return -1

error112:
Return -2

error113:
Return -3

error114:
Return -4
GO

 

///////////////////利用游标

IF len(@Parid)=25 RETURN -1
DECLARE @execsql [VARCHAR](500)
DECLARE @szTypeId VARCHAR(25),@sonnum INT,@par VARCHAR(25),@soncount INT
SELECT @execsql=' SELECT typeid,sonnum,parid,soncount FROM '
+@dbname+' WHERE typeid= '+''''+@parid+''''

DECLARE checkid_CURSOR CURSOR FOR
EXEC (@execsql)
OPEN checkid_CURSOR
FETCH NEXT FROM checkid_CURSOR INTO @szTypeId, @sonnum, @par,@soncount
WHILE (@@FETCH_STATUS = 0)
Begin
Set @nSon=@sonnum
Set @nCount=@soncount
DECLARE @tempId VARCHAR(5),@nReturn INT
Set @soncount=@soncount+1
EXEC @nReturn=inttostr @soncount,@tempId out
IF @nReturn=-1
BEGIN
CLOSE checkid_CURSOR
DEALLOCATE checkid_CURSOR
RETURN -2
END
ELSE
BEGIN
IF @sztypeid='00000'
Set @createdid=@tempId
ELSE
Set @createdid=RTRIM(@szTypeid)+@tempId
END
FETCH NEXT FROM checkid_CURSOR INTO @szTypeId, @sonnum, @par,@soncount
End
CLOSE checkid_CURSOR
DEALLOCATE checkid_CURSOR
RETURN 1

视频教程列表
文章教程搜索
 
Sql Server推荐教程
Sql Server热门教程
看全部视频教程
购买方式/价格
购买视频教程: 咨询客服
tel:15972130058