SQL function

CREATE FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0)
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END

SET DATEFIRST 1
SELECT employee.dbo.ISOweek('12/26/1999') AS 'ISO Week'



create function employeefun()
returns table
as
return(select * from employee)
 
select * from employeefun()



create function employeeupdate()
returns int
as
begin
DECLARE @id int
set @id=(select id from employee where id=1)
return @id
end

select  employee.dbo.employeeupdate() as no
select * from  employee where id=employee.dbo.employeeupdate() 






CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
      empname nvarchar(50),
      mgrid nchar(5) REFERENCES employees(empid),
      title nvarchar(30)
      )

select * from employees

CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
   empname nvarchar(50) NOT NULL,
   mgrid nchar(5),
   title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
   DECLARE @RowsAdded int
   -- table variable to hold accumulated results
   DECLARE @reports TABLE (empid nchar(5) primary key,
      empname nvarchar(50) NOT NULL,
      mgrid nchar(5),
      title nvarchar(30),
      processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
   INSERT @reports
   SELECT empid, empname, mgrid, title, 0
   FROM employees
   WHERE empid = @InEmpId
   SET @RowsAdded = @@rowcount
   -- While new employees were added in the previous iteration
   WHILE @RowsAdded > 0
   BEGIN
      /*Mark all employee records whose direct reports are going to be
   found in this iteration with processed=1.*/
      UPDATE @reports
      SET processed = 1
      WHERE processed = 0
      -- Insert employees who report to employees marked 1.
      INSERT @reports
      SELECT e.empid, e.empname, e.mgrid, e.title, 0
      FROM employees e, @reports r
      WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
      SET @RowsAdded = @@rowcount
      /*Mark all employee records whose direct reports have been found
   in this iteration.*/
      UPDATE @reports
      SET processed = 2
      WHERE processed = 1
   END
  
   -- copy to the result of the function the required columns
   INSERT @retFindReports
   SELECT empid, empname, mgrid, title
   FROM @reports
   RETURN
END
GO

SELECT *
FROM fn_FindReports('1')
Labels: