12,816,270 members (33,351 online)
Rate this:
See more:
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
Updated 22-Mar-14 0:26am
v2

Rate this:

## Solution 1

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

```
v2
Rate this:

Rate this:

## 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[^]

Top Experts
Last 24hrsThis month
 Jochen Arndt 215 Richard Deeming 175 Karthik Bangalore 174 Dave Kreskowiak 140 ppolymorphe 105
 OriginalGriff 4,427 Graeme_Grant 3,649 Karthik Bangalore 3,386 Jochen Arndt 2,391 ppolymorphe 2,367