Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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
Posted 22-Mar-14 0:11am
Edited 22-Mar-14 0:26am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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

  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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
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'
Read more: Returning Data by Using OUTPUT Parameters[^]
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 7,442
1 OriginalGriff 5,996
2 Peter Leow 4,292
3 CHill60 2,858
4 Mika Wendelius 2,850


Advertise | Privacy | Mobile
Web04 | 2.8.150224.1 | Last Updated 22 Mar 2014
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100