Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 C# ASP.NET SQL , +
How to convert the following SQL-C# logic to syntactically-correct SQL code (SQL Server 2005)?
 
"Description", "LeaveType", "MonthlyMaxLeave", "YearlyMaxLeave" are the column names in the table EmpTab.
 
I have written a basic logic to calculate the PayLeave and NonPayLeave, and to display its final values. Assume that the fields "Description" and "LeaveType" always contains the values "Sick" and "Yearly" respectively. Please do not worry about the data in the table. The field values in the table may be customized as per the requirements.
 
Just correct the syntax in the code so as to meet the SQL standards, as well as to display the PayLeave and NonPayLeave values based on the calculation logic shown below.
USE [Emp1]
GO
CREATE FUNCTION [dbo].[Leave] 
(  
   @EmployeeID INT    
)
RETURNS INT  
AS  
BEGIN 
 
    DECLARE	@PayLeave INT,
    	@NonPayLeave INT;
    
    SET @PayLeave = 0;
    SET @NonPayLeave = 0;
    
    RETURN
    (
        SELECT
        IF((ImDescription == "Sick") AND (ImLeaveType == "Yearly"))
        {
            IF((ImMonthlyMaxLeave > 1) OR (ImYearlyMaxLeave > 10 ))
            {
            	@NonPayLeave = @NonPayLeave + 1;
            	PRINT @NonPayLeave;
            }		
            ELSE
            {	
            	@PayLeave = @PayLeave + 1;
                PRINT @PayLeave;
            }
        }
    )
END
 
--PRINT dbo.Leave('123')
Posted 14-Mar-13 21:46pm
Edited 14-Mar-13 22:09pm
(no name)17.4K
v2
Comments
Karthik Harve at 15-Mar-13 3:10am
   
[Edit] pre tags added.
Maciej Los at 6-Apr-13 16:55pm
   
Please, be more specific and provide more details.
Are you trying to count NonPayLeave and PayLeave for employee with EmployeeID?

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

  Print Answers RSS
0 Maciej Los 310
1 OriginalGriff 285
2 Afzaal Ahmad Zeeshan 200
3 Sergey Alexandrovich Kryukov 195
4 BillWoodruff 180
0 OriginalGriff 6,499
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,193
3 Manas Bhardwaj 4,657
4 Maciej Los 4,120


Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 15 Mar 2013
Copyright © CodeProject, 1999-2014
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