数据表
PublicID(varchar) PiblicDesc(Numeric)
------------------------------------------------------
GoodsID 100001
OrderID 1001
Create Procedure dbo.SP_PublicID
@PublicCode varchar(10)
As
Declare @PublicNO varchar(13)
Begin
Update PublicNo set PublicDesc=PublicDesc+1 where Publicid=@PublicCode
--读取新的编号值
End
GO小弟刚学求大虾帮忙按piclicid值更新表中数字然后读取新值
PublicID(varchar) PiblicDesc(Numeric)
------------------------------------------------------
GoodsID 100001
OrderID 1001
Create Procedure dbo.SP_PublicID
@PublicCode varchar(10)
As
Declare @PublicNO varchar(13)
Begin
Update PublicNo set PublicDesc=PublicDesc+1 where Publicid=@PublicCode
--读取新的编号值
End
GO小弟刚学求大虾帮忙按piclicid值更新表中数字然后读取新值
@PublicCode varchar(10)
As
Declare @PublicNO varchar(13)
Begin
Update PublicNo set PublicDesc=PublicDesc+1 where Publicid=@PublicCode
--读取新的编号值
IF @@rowcount>1
select PiblicDesc from PublicNo
End
GO
@PublicCode varchar(10) ,@PublicNO varchar(13) output
As
Begin
Update PublicNo set @PublicNO = PublicDesc=PublicDesc+1 where Publicid=@PublicCode
--读取新的编号值
End
GO
PublicCode (varchar) PublicDesc(Numeric)值
OrderID 10000
'----------------------------------------------------------------------------------
Create Procedure dbo.SP_PublicID
更新PublicDesc中的值 PublicDesc=PublicDesc+1
输出PublicDesc中的最新值 也就是10001
这中间怎么写,大虾帮帮忙,都是错误
GO
Set Rs=Conn.Execute(StrSQL)
OrderID = Rs("PubID")
Rs.Close
在网页中读取
use tempdb
Go
if object_id('NextSerialNo') Is Not Null Drop Table NextSerialNo
Go
if object_id('SerialTypeMTR') Is Not Null Drop Table SerialTypeMTR
Go
Create Table SerialTypeMTR /*號碼類型*/
(
ID smallint Identity ,
Code nvarchar(50),
Constraint PK_SerialTypeMTR_ID Primary Key(ID)
)
Go
Create Table NextSerialNo /*流水號*/
(
ID smallint Identity ,
SerialTypeID smallint Not Null, --號碼類型
NextNo smallint, --下一個流水號
Length smallint, --流水號長度
Constraint PK_NextSerialNo_ID Primary Key(ID),
Constraint FK_NextSerialNo_SerialTypeID Foreign Key(SerialTypeID) References SerialTypeMTR(ID)
)
Go
Insert Into SerialTypeMTR(Code) Values(N'SO')
GO
Insert Into NextSerialNo(SerialTypeID,NextNo,Length) Values(1,1,5)
GO
--取號存儲過程
if object_id('rSerialNo') Is Not Null
Drop Proc rSerialNo
Go
Create Proc rSerialNo
(
@SerialTypeID smallint, --號碼類型
@Rowcount smallint --返回的號碼數量
)
As
Set Nocount OnBegin Try
Begin tran
Declare @SeriaTB Table(SerialTypeID smallint,NextNo smallint,Length smallint)
Update NextSerialNo
Set NextNo=NextNo+@Rowcount
Output deleted.SerialTypeID,deleted.NextNo,deleted.Length
Into @SeriaTB(SerialTypeID,NextNo,Length)
Where SerialTypeID=@SerialTypeID
;With CTE_SerialNo As
(
Select Cast(0 As smallint) As Row union All
Select Cast(Row+1 As smallint) As Row From CTE_SerialNo Where Row<@Rowcount-1
)
Select b.Code+Replicate('0',a.Length-len(a.NextNo+c.Row))+Rtrim(a.NextNo+c.Row) As SerialNo
From @SeriaTB As a
Inner Join SerialTypeMTR As b On b.ID=a.SerialTypeID
Cross Join CTE_SerialNo As c
Commit Tran
End Try
Begin Catch
Declare @Error nvarchar(2047)
Set @Error=Error_message()
Raiserror 50001 @Error
Rollback Tran
End Catch
Go
Select * From NextSerialNo --取號Exec rSerialNo
@SerialTypeID = 1,
@Rowcount = 14Select * From NextSerialNoGo
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]过程 'SP_PublicID' 需要参数 '@PublicIDNO',但未提供该参数。 /test1.asp,行 4
create table MyPublicNO
(PublicCode varchar(20),PublicDesc numeric)insert into MyPublicNO
select 'OrderID',10000 union all
select 'GoodsID',12345
-- 建存储过程
create procedure dbo.SP_PublicID
(@PublicCode varchar(10))
as
begin
declare @t table(PublicDesc numeric) update MyPublicNO
set PublicDesc=PublicDesc+1
output inserted.PublicDesc into @t(PublicDesc)
where PublicCode=@PublicCode
--读取新的编号值
select PublicDesc from @t
end
-- 测试1
exec dbo.SP_PublicID @PublicCode='OrderID'
/*
PublicDesc
---------------------------------------
10001(1 row(s) affected)
*/
-- 测试2
exec dbo.SP_PublicID @PublicCode='OrderID'
/*
PublicDesc
---------------------------------------
10002(1 row(s) affected)
*/
-- 测试3
exec dbo.SP_PublicID @PublicCode='GoodsID'
/*
PublicDesc
---------------------------------------
12346(1 row(s) affected)
*/
PublicCode (varchar) PublicDesc(Numeric)值
OrderID 10000
'----------------------------------------------------------------------------------
Create Procedure dbo.SP_PublicID
更新PublicDesc中的值 PublicDesc=PublicDesc+1
输出PublicDesc中的最新值 也就是10001
这中间怎么写,大虾帮帮忙,都是错误
GO像这样在网页中使中
StrSQL="SP_Publicid 'OrderID'"
Set Rs=Conn.Execute(StrSQL)
OrderID = Rs("PubID")
Rs.Close