USE pubs

--Table valued inline function 
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
      FROM sales s, titles t
      WHERE s.stor_id = @storeid and
      t.title_id = s.title_id) 

--Call the function
Select * from SalesByStore(6380)

--Scalar function, returns the week number for any date
CREATE FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   --Get the week number by subtracting the 4th Jan. week number
   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

--Call the function
SET DATEFIRST 2 --Set first week of year
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'