北京赛车单双技巧 北京赛车pk10黑客软件 北京pk10计划免费软件 pk10前二做号工具 pk10专业预测 pk10民间高手 北京pk10挂机选号经验分享模式 pk10冠军3码倍投计划 北京赛车计划app手机版 北京赛车开奖软件 pk10最牛稳赚模式最新 pk10全天免费计划 易算北京pk10准不准 pk10赛车7码技巧 pk10八码滚雪球3期一收

存儲過程使用技巧

11/16/2006來源:SQL技巧人氣:11843

================以下是存儲過程================
/*
加入一條基本信息
庫存商品

-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][email protected]_1 or ([fullname][email protected]
and [usercode][email protected]) and [deleted]<>1) goto error114
end
if @checkValue=2--全名不能完全相同
begin
if exists(Select [typeid] From ptype Where [typeId][email protected]_1 or ([fullname][email protected]
) and [deleted]<>1) goto error114
end
if @checkValue=3--編號不能完全相同
begin
if exists(Select [typeid] From ptype Where [typeId][email protected]_1 or (
[usercode][email protected]) and [deleted]<>1) goto error114
end
if @checkValue=4 -- 查找編號和全名不能完全相同
begin
if exists(Select [typeid] From ptype Where [typeId][email protected]_1 or ([fullname][email protected]
or [usercode][email protected]) and [deleted]<>1) goto error114
end
if (len(@Barcode)>0)
begin
if exists(Select * From ptype
Where ([Barcode][email protected]) and [deleted]<>1) return -5
end

BEGIN TRAN InsertP

Select @leveal=[leveal] From ptype Where [typeid][email protected]
Select @[email protected]+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][email protected]+1,[soncount][email protected]+1
Where [typeid][email protected]
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 '
[email protected]+' WHERE typeid= '+''''[email protected]+''''

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 @[email protected]
Set @[email protected]
DECLARE @tempId VARCHAR(5),@nReturn INT
Set @[email protected]+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 @[email protected]
ELSE
Set @createdid=RTRIM(@szTypeid)[email protected]
END
FETCH NEXT FROM checkid_CURSOR INTO @szTypeId, @sonnum, @par,@soncount
End
CLOSE checkid_CURSOR
DEALLOCATE checkid_CURSOR
RETURN 1



pk10有什么方法平刷
北京赛车单双技巧 北京赛车pk10黑客软件 北京pk10计划免费软件 pk10前二做号工具 pk10专业预测 pk10民间高手 北京pk10挂机选号经验分享模式 pk10冠军3码倍投计划 北京赛车计划app手机版 北京赛车开奖软件 pk10最牛稳赚模式最新 pk10全天免费计划 易算北京pk10准不准 pk10赛车7码技巧 pk10八码滚雪球3期一收
怎么做广告推广赚钱 9994455福彩3d论坛 宁夏十一选五计划 土木工程什么职业赚钱 玩北京pk10久玩必输 时时彩稳赚不赔投注法 福建十一选五走势图 ag猛龙传奇游戏技巧 星空斗地主官网