how to create Multi-Statement Table Valued function in sql server .

Anonymous User 2022 16 Oct 2015

Can anyone please help me how to solve this problem.

Anonymous User

Other

I am a content writter !

1 Answers

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

forum

how to create Multi-Statement Table Valued function in sql server .

Can you answer this question?

1 Answers