Actually I have created a User defined table type in sql server 2008. Structure is given below.
I am passing it as parameter in function and that function also return that type of table type. I am facing the problem, while I declare a variable of that type in function, inserting some data in it and return that parameter.
Table type structure is:
Create Type OfferWithSubscription as Table
(
OfferID int,
OfferUserID int,
OfferImage varchar(200),
OfferExactPrice Decimal(18,2),
OfferContent varchar(max),
OfferTitle varchar(100),
StartDate datetime,
EndDate datetime,
StartTime datetime,
StopTime datetime,
ShowToUser bit,
SubID int,
SubLevel varchar(100)
)
And the function, what I am trying to create is:
CREATE FUNCTION FN_ShowOffer
(
@Gold int,
@Silver int,
@Bronze int,
@table dbo.OfferWithSubscription Readonly )
RETURNS dbo.OfferWithSubscription
AS
BEGIN
DECLARE @ReturnTable AS dbo.OfferWithSubscription;
Declare @Case as varchar(20)
if(@Gold=0 and @Silver=1 and @Bronze=0 )
begin
set @Case='1S'
end
if(@Case='1S')
Begin
insert into @ReturnTable
select OfferID, OfferUserID, OfferImage,
OfferExactPrice, OfferContent,
OfferTitle, StartDate, EndDate,
StartTime, StopTime, ShowToUser,
SubID, SubLevel
from @table
where SubID=4
End
RETURN (
@ReturnTable
)
END
You'll just have to expand the type like below.
FYI - Can T-SQL function return user-defined table type?
CREATE FUNCTION FN_ShowOffer
(
@Gold int,
@Silver int,
@Bronze int,
@table dbo.OfferWithSubscription Readonly )
RETURNS @ReturnTable Table
(
OfferID int,
OfferUserID int,
OfferImage varchar(200),
OfferExactPrice Decimal(18,2),
OfferContent varchar(max),
OfferTitle varchar(100),
StartDate datetime,
EndDate datetime,
StartTime datetime,
StopTime datetime,
ShowToUser bit,
SubID int,
SubLevel varchar(100)
)
AS
BEGIN
Declare @Case as varchar(20)
if(@Gold=0 and @Silver=1 and @Bronze=0 )
begin
set @Case='1S'
end
if(@Case='1S')
Begin
insert into @ReturnTable
select OfferID,OfferUserID,OfferImage,OfferExactPrice,OfferContent,OfferTitle,
StartDate,EndDate,StartTime,StopTime,ShowToUser,SubID,SubLevel from @table where SubID=4
End
RETURN
END
And to further clarify, that's fully compatible and assignable to a variable of that table type, e.g. SQL Fiddle
declare @t OfferWithSubscription
insert @t
select * from fn_showoffer(1,2,3,@t)