如何得到存儲過程返回值呢? 100分
如下存儲過程,@autocode 為根據日期進行自動編號,要返回這個編號
CREATE PROCEDURE [dbo].[update_po_head]
@code char(12),
@ddate datetime,
@prepared varchar(50),
@messrs varchar(50),
@attn varchar(50),
@delivery varchar(50),
@delivery_sch varchar(50),
@payment varchar(50),
@remark varchar(100),
@return char(15) OUTPUT
AS
if @code<>'NEW'
begin
update dbo.po_head set ddate=@ddate,prepared=@prepared,messrs=@messrs,attn=@attn,delivery=@delivery,delivery_sch=@delivery_sch,payment=@payment,remark=@remark where code=@code
end
else
begin
declare @autocode char(12)
select @autocode=max(code) from po_head --where code like rtrim(right(year(getdate()),2))+'%'
if @autocode is null
select @autocode='PW'+rtrim(right(year(getdate()),3))+'SC'+'00001'
else
begin
set @autocode=cast(right(rtrim(@autocode),5) as int)+1
set @autocode='CW'+rtrim(right(year(getdate()),3))+'SC'+rtrim(REPLICATE('0',5 - len(@autocode))+@autocode)
end
insert into dbo.po_head(code,ddate,prepared,messrs,attn,delivery,delivery_sch,payment,remark)
values(@autocode,@ddate,@prepared,@messrs,@attn,@delivery,@delivery_sch,@payment,@remark)
set @return=@autocode
end
GO
前端用VB,就是返不回值:
Dim cmdExecute As New ADODB.Command
'CN 是連接 connection對象,設為 aduserclient
strSQL = "'" & Trim(txtCode.Text) & "','" & Format(txtddate.Text, "YYYY/MM/DD") & "', " _
& " '" & txtprepared.Text & "','" & txtmessers.Text & "', " _
& " '" & txtattn.Text & "','" & txtdelivery.Text & "','" & txtDelivery_sch.Text & "','" & txtpayment.Text & "','" & txtremark.Text & "'"
strSQL = "update_po_head " & strSQL & ""
With cmdExecute
.ActiveConnection = CN
.CommandText = strSQL
.CommandType = adCmdText
.Parameters.Append .CreateParameter(, adChar, adParamOutput, 15)
End With
cmdExecute.Execute
請大家幫忙,
另想問一下,這種累加自動編號,是在客戶端完成呢,還是在服務器完成好呢,謝了