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

Total Post:341

Points:2389
Posted on    October-16-2015 11:01 PM

 MSSQL Server SQL Server  SQL  SQL Server 2008  SQL Server 2012 
Ratings:


 1 Reply(s)
 409  View(s)
Rate this:
Can anyone please help me how to solve this problem.


aditya kumar Patel

Total Post:250

Points:1782
Posted on    October-16-2015 11:12 PM



--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


Modified On Oct-16-2015 11:12:52 PM

Don't want to miss updates? Please click the below button!

Follow MindStick