HOW TO CREATE MULTI-STATEMENT TABLE VALUED FUNCTION IN SQL SERVER .

Ankit Singh

Total Post:341

Points:2389
Posted by  Ankit Singh
 663  View(s)
Ratings:
Rate this:
Can anyone please help me how to solve this problem.
  1. aditya kumar Patel

    Post:254

    Points:1810
    Re: how to create Multi-Statement Table Valued function in sql server .

    
    
    

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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!