如何从程序中返回值 [英] how to return value from procedure

问题描述

我有以下商店程序..

我想要返回变量值p，na，sh，fh，na

plz帮我做什么。 。

I have following store procedure..
I want to return values of variables p,na,sh,fh,na
plz help me what shoulb be done..

ALTER PROCEDURE [dbo].[sp_attendence]
	@empid nvarchar(100),
	@year int,
	@month int
AS
BEGIN
	
	SET NOCOUNT ON;
	

	declare @anoon bit;
	declare @bnoon bit;
	declare @absent bit;
	declare @attendence int;
	
	declare @fh int;
	declare @a int;
	declare @sh int;
	declare @p int;
	declare @i int
	declare @na int
	declare @noofdays int;
	
	set @i=1;
	set @fh=0
	set @sh=0
	set @a=0;
	set @p=0;
	set @na=0;
	
	SELECT @noofdays=dbo.udf_GetNumDaysInMonth(@month,@year);

	while(@i<=@noofdays)
	begin
	select @anoon =anoon,@bnoon=bnoon,@absent=absent from dbo.sms_hr_EmpAttendence where empid=@empid and year=@year and month=@month and day=@i
	
	if(@anoon ='1' and @bnoon='1')
	begin
		set @p=@p+1;
	end
	
	else if(@bnoon ='1' and @anoon='0')
		begin
			SET @fh=@fh+1
		end
		
	ELSE if(@bnoon='0' and @anoon='1')
	begin
		set @sh=@sh+1
	end
	
	else if(@bnoon='0' and @anoon ='0')
	begin
		set @a=@a+1
	end
	
	else
	begin
	set @na=@na+1;
	end
	 
	
	set @i=@i+1;
	end

    
END

推荐答案

select @p;
select @na;
select @sh;
select @fh;
select @na;

尝试

http://technet.microsoft.com/en -us / library / ms378108.aspx [ ^ ]

http://www.toadworld.com/platforms/sql-server/w/wiki/10261.stored-procedures-output-parameters-return-values.aspx [ ^ ]

http://www.infinitezest.com/articles/executing-a-stored-procedure-with-o utput-parameters-from-query-window.aspx [ ^ ]

Try
http://technet.microsoft.com/en-us/library/ms378108.aspx[^]
http://www.toadworld.com/platforms/sql-server/w/wiki/10261.stored-procedures-output-parameters-return-values.aspx[^]
http://www.infinitezest.com/articles/executing-a-stored-procedure-with-output-parameters-from-query-window.aspx[^]

您必须使用OUTPUT关键字的参数。您的存储过程应如下所示：

Hi,

You have to use parameters with OUTPUT keyword. Your stored procedure should be like this:

ALTER PROCEDURE [dbo].[sp_attendence]
	@empid nvarchar(100),
	@year int,
	@month int,
        @p int output,
        @na int output,
        @sh int output,
        @fh int output
AS
BEGIN
	
	SET NOCOUNT ON;
	
 
	declare @anoon bit;
	declare @bnoon bit;
	declare @absent bit;
	declare @attendence int;
	
	declare @fh int;
	declare @a int;
	declare @sh int;
	declare @p int;
	declare @i int
	declare @na int
	declare @noofdays int;
	
	set @i=1;
	set @fh=0
	set @sh=0
	set @a=0;
	set @p=0;
	set @na=0;
	
	SELECT @noofdays=dbo.udf_GetNumDaysInMonth(@month,@year);
 
	while(@i<=@noofdays)
	begin
	select @anoon =anoon,@bnoon=bnoon,@absent=absent from dbo.sms_hr_EmpAttendence where empid=@empid and year=@year and month=@month and day=@i
	
	if(@anoon ='1' and @bnoon='1')
	begin
		set @p=@p+1;
	end
	
	else if(@bnoon ='1' and @anoon='0')
		begin
			SET @fh=@fh+1
		end
		
	ELSE if(@bnoon='0' and @anoon='1')
	begin
		set @sh=@sh+1
	end
	
	else if(@bnoon='0' and @anoon ='0')
	begin
		set @a=@a+1
	end
	
	else
	begin
	set @na=@na+1;
	end
	 
	
	set @i=@i+1;
	end
 
    
END

此外，您必须使用OUTPUT关键字执行此过程以及这些参数。

示例：

Also, you have to execute this procedure with these parameters using OUTPUT keyword.
Example:

DECLARE @p  INT,
        @na INT,
        @sh INT,
        @fh INT

EXEC [dbo].[sp_attendence]
  '100',
  2014,
  3,
  @p = @p OUTPUT,
  @na = @na OUTPUT,
  @sh = @sh OUTPUT,
  @fh = @fh OUTPUT

SELECT @p  AS N'@p',
       @na AS N'@na',
       @sh AS N'@sh',
       @fh AS N'@fh'

了解详情：使用OUTPUT参数返回数据 [ ^ ]

这篇关于如何从程序中返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！