Inserting Rows Into Table Using Function In Sql Server 2008 R2

Praveen Singh         No comments

Based on the return values, we can divide the function in 3 type :

  1. Scalar function
  2. Inline table valued function
  3. Multistatement table valued function
Often people talk about whether we can use function to insert values into tables.
Well, there is a way.I know a lot of people use function just to return a single value from function (that's Scalar function).May be that's the reason they are not aware of other usage of function.

So, let's see with a very simple example....

This is nothing but a Multistatement table valued function....

Points To Remember:

Multistatement table-valued functions allow a developer to dynamically populate a table in multiple steps that are similar to those of a stored procedure, but they can be referenced as tables in a SELECT statement.

When using a multistatement table-valued function, the structure of the table must be defined in the header of the function. A variable name is used for the table, and all operations that modify data can reference only the table variable.

CREATE FUNCTION MULTIROWS ()

/*********** @temp variable of TABLE type********/

RETURNS @temp TABLE
(
    NAME VARCHAR(20),
    ID            INT,
    CITY VARCHAR(20)
)
AS
BEGIN

/*********** Refer temp variable for inserting data ********/

        INSERT INTO @temp VALUES
        ('ROX',1,'NY'),
        ('JOY',2,'LKO'),
        ('ANT',3,'DL')
       
    RETURN
END
GO


Get Results :

SELECT * FROM MULTIROWS()


Published by Praveen Singh

A blogger by passion.You can find me tucked in my bed and blogging on weekends when not roaming around. Besides blogging, I love music and you can find my songs on my fb page:PraveenUnplugged
.
Follow on Youtube : Videos On Latest Happenings |ThingsToKnow
.
Follow us Talend In Action

0 responses:

© 2015 Techie's House. Designed by Bloggertheme9