Tuesday, 17 July 2012

How to create Table-valued functions in sql


create FUNCTION [dbo].[fnTableType]   
(    
 @userID int
)   
RETURNS @UserNetworks TABLE    
(   
 [userID] [int],   
 [title] [nvarchar](512),
 [created] [datetime] ,   
 [modified] [datetime] ,
 [active] [bit]
)   
AS   
BEGIN   

 Insert @UserNetworks      
 Select  N.userID, UNM.title, N.created,N.modified,N.active from dbo.tblUser N
        inner join dbo.tblUserMembership UNM  on UNM.userId = N.UserID     
     where unm.userID = @userID
Return    
END   

OR

create FUNCTION [dbo].funTableType   
( @UserID int )   
RETURNS TABLE    
AS   
RETURN    
(Select * from dbo.tblUsers where active =1  and userID =  @UserID )

No comments:

Post a Comment