By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,500 Members | 1,250 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,500 IT Pros & Developers. It's quick & easy.

How to convert the SQL-C# logic to syntactically-correct SQL code (SQL Server 2005)?

P: 4
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.



Expand|Select|Wrap|Line Numbers
  1. USE [Emp1]
  2. GO
  3.  
  4.  
  5. CREATE FUNCTION [dbo].[Leave] 
  6. (  
  7.    @EmployeeID INT    
  8. )
  9. RETURNS INT  
  10. AS  
  11.  
  12. BEGIN 
  13.  
  14. DECLARE    @PayLeave INT,
  15.     @NonPayLeave INT;
  16.  
  17. SET @PayLeave = 0;
  18. SET @NonPayLeave = 0;
  19.  
  20. RETURN
  21. (
  22.     SELECT
  23.     IF((ImDescription == "Sick") AND (ImLeaveType == "Yearly"))
  24.     {
  25.         IF((ImMonthlyMaxLeave > 1) OR (ImYearlyMaxLeave > 10 ))
  26.         {
  27.             @NonPayLeave = @NonPayLeave + 1;
  28.             PRINT @NonPayLeave;
  29.         }        
  30.         ELSE
  31.         {    
  32.             @PayLeave = @PayLeave + 1;
  33.             PRINT @PayLeave;
  34.         }
  35.     }
  36. )
  37.  
  38. END
  39.  
  40.  
  41.  
  42. --PRINT dbo.Leave('123')
  43.  
  44.  
  45.  
Mar 15 '13 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,430
This is not a code writing service but if you post your attempt at the conversion, we can help guide you to a solution.
Mar 15 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.