CODEWITHSUNDEEP
sql-server-2008
Losst Soul
Losst Soul

Reputation: 1

How to declare table in SQL Server?

I am trying to create a function which needs to return a table but even function is not made too and I need to return the resulted table.

My script is like this 

create function FNC_getPackageListById(@PkId int )
returns table
as
      return
        if exists (select Date1, Date2 from PromotionPackage 
                   where PkId = @PkId and Date1 is null and Date2 is null)
        begin
            select Rate,Remarks,PackageName from Package where PkId=@PkId
        end
        else
        begin
            select p.Rate,
                   p.Remarks,
                   p.PackageName,
                   pp.Date1,
                   pp.Date2
             from PromotionPackage pp,
                 Package p 
             where pp.PkId=p.PkId and  p.PkId=@PkId
       end 
   end

Upvotes: 0

Views: 191

Answers (1)

vendettamit
vendettamit

Reputation: 14677

The function called table valued function that returns a table. See this example:

CREATE FUNCTION TrackingItemsModified(@minId int)
RETURNS @trackingItems TABLE (
   Id       int      NOT NULL,
   Issued   date     NOT NULL,
   Category int      NOT NULL,
   Modified datetime NULL
) 
AS
BEGIN
   INSERT INTO @trackingItems (Id, Issued, Category)
   SELECT ti.Id, ti.Issued, ti.Category 
   FROM   TrackingItem ti
   WHERE  ti.Id >= @minId; 

   RETURN;
END;

Upvotes: 1

Related Questions