How to get output 'Month' and 'Year'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to get output 'Month' and 'Year'

Author

Topic

abdbari
Starting Member

7 Posts

Posted - 2013-03-27 : 03:24:55

ID StaffNo Month Year LeaveStatus LeaveTaken
---- ------ ----- ---- ----------- ----------
00001 MC 1
00002 MC 2
00003 MC 3
00004 MC 4

e.g Month ->1 Year ->2013

Precedure as below:

CREATE PROCEDURE [dbo].[LEAVE_TAKEN]
( @Organisation_Code GLCOMPANY,
@Location_Code GLLOCN,
@Year SMALLINT,
@Month SMALLINT
)

AS
BEGIN

CREATE TABLE #Leave_Taken_Summ
( Id Char(4) Null,
StaffNo EMPNUM ,
Department FUNCDESC ,
Month varchar(35) ,
Year varchar(35) ,
LeaveStatus char(10) Null,
LeaveTaken int

)

INSERT INTO #Leave_Taken_Summ
( StaffNo ,
Department ,
LeaveStatus ,
LeaveTaken
)
d.C02_Dept ,
a.C03_lve_code,
sum(a.C04_lve_Taken)/8 as 'Total Taken'
FROM LEAVE_TRANS a,
H01_identfcatn b
where a.StaffNo = b.StaffNo
AND a.StaffNo = c.StaffNo
and a.C03_lve_code in ('MC')
and datepart(yy,a.L04_Start_date) = @year
and datepart(mm,a.L04_Start_date) = @month
group by b.StaffNo,b.C02_Dept ,a.C03_lve_code
ORDER BY b.StaffNo

END

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-27 : 04:38:58

Do you want @Month and @Year as OUTPUT Params for Procedure?
If yes,
CREATE PROCEDURE [dbo].[LEAVE_TAKEN]
( @Organisation_Code GLCOMPANY,
@Location_Code GLLOCN,
@Year SMALLINT OUT,
@Month SMALLINT OUT
)
AS
BEGIN
END

--Execution of above procedure
DECLARE @Year SMALLINT = 2013, @Month SMALLINT = 1
EXEC [dbo].[LEAVE_TAKEN] inputParamsValues, @Year OUT, @Month OUT
SELECT @Year, @Month

abdbari
Starting Member

7 Posts

Posted - 2013-03-27 : 05:32:39

Now after execution the procedure:

only showings

staffno,
Department,
Leave status,
Leave taken,

--------------------
except for month and Year.

and datepart(yy,a.L04_Start_date) = 2013 ->enter manually.
and datepart(mm,a.L04_Start_date) = 1 ->enter manually.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-27 : 07:11:50

Why these two Month varchar(35) , Year varchar(35)are Characters in #Leave_Taken_Summ?

abdbari
Starting Member

7 Posts

Posted - 2013-03-28 : 02:49:28

i wrongly entered.
it should be Month varchar(4) , Year varchar(4)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-03-28 : 04:45:01

What is your question actually ?

You show us the stored procedure LEAVE_TAKEN. However, in that procedure, you create a temp table #Leave_Taken_Summ then insert some records into it and that's it. Do note that, the temp table will be automatically dropped when the stored procedure ended. What do you expect the stored procedure to do ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources