Ellen-小人物的心聲

如何在Sql Server中取 StoreProcedure的值

假設做一個取號檔[iSNo][sUKEY]   [iSNo]設定一identity在insert一筆UKEY　時自動加取號加一

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HRLeaveNum]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GetNumber]
GO

CREATE TABLE [dbo].[GetNumber](
 [iSNo] [int] IDENTITY (1, 1) NOT NULL ,
 [sUKEY] [varchar] (80) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL
) ON [PRIMARY]
GO

如何在Sql Server中取 StoreProcedure回傳的值
方法一用傳output的參數
--------------------------------------------------------
CREATE PROCEDURE [dbo].[spa_GetID]
@RANDID varchar(4),
@ret char(10) output
AS
DECLARE @v_RandID  as varchar(50)  --取得insert的亂數
DECLARE @v_iSno AS integer   --取得序號

select @v_RandID= rtrim(rtrim(convert(char,getdate(),13)) +  convert(char,RAND(@RANDID)))　　’日期加上傳入變數的值進行亂數取號
if @v_RandID<>''   --判斷insert的亂數是否有取到？
begin
 insert into HRLeaveNum(sUKEY) values(@v_RandID)
 select @ret=iSNo from [GetNumber]where sUKEY=@v_RandID
 return
end

GO

-- 在Query Analyzer取sp內回傳的值 語法
declare @output as char(50)
EXEC spa_GetID  '3209',@output  output
PRINT @output
--取sp內回傳的值

-------------------------------------------------
不用丟接的output的變數 直Return值

CREATE PROCEDURE [dbo].[spa_GetID]
@I_EmpID varchar(4)
AS
DECLARE @v_RandID  as varchar(50)
DECLARE @v_iSno AS integer
DECLARE @ret AS integer

select @v_RandID= rtrim(rtrim(convert(char,getdate(),13)) +  convert(char,RAND(3209)))
if @v_RandID<>''   --取得insert的亂數
begin
 insert into [GetNumber] (sUKEY) values(@v_RandID)
 select @ret=iSNo from [GetNumber]where sUKEY=@v_RandID
 return @ret
end

GO

--取sp內回傳的值
DECLARE @intRow int
EXEC @intRow = spa_GetLeaveRqID '3209'
SELECT @intRow
--取sp內回傳的值