forum

Home / DeveloperSection / Forums / how to create Multi-Statement Table Valued function in sql server .

Ask Question
Anonymous User

Anonymous User

Follow
I am a content writter !

Can you answer this question?

Answer

1 Answers

Anonymous User

Anonymous User

16-Oct-2015 


--let’s start querying create a table.

 CREATE TABLE UserMasters(

       UserId int IDENTITY(1,1) primary key,

       LoginName nvarchar(max) ,

       password nvarchar(max) ,

       UserName nvarchar(max)  
  )


 CREATE TABLE ProductMasters(


       ProductId int IDENTITY(1,1) primary key,

       ProductName nvarchar(max) ,

       Price decimal(18, 2)  ,

       CategoryId int  , 

       Userid int

 )


 --We will now create a table valued function, which returns
the total of price filtered by a given category . Write the following code in
your query pad – 

 create function fnGetUsernamewithProductMasters

(  

 @categoryId int

)

returns @customerwithproduct
table(

UserName varchar(100),

ProductName varchar(100),

Price decimal(10,2)

)

as

begin

insert @customerwithproduct

select   b.UserName, a.ProductName,a.Price   from ProductMasters as a inner join UserMasters as b on a.Userid=b.Userid where CategoryId=@categoryId 

return

end 


--The above function returns an table.
To test this function, we will write some code as shown below – 

select * from dbo.fnGetUsernamewithProductMasters(1)  



Result
answer 415871610111211

Liked By