By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,361 Members | 1,412 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,361 IT Pros & Developers. It's quick & easy.

multi-statement table valued UDF

P: 22
Three types of Functions are there-
1)Simple Scalar function-It returns a scalar value.

2)Inline-Table Valued UDF-
It allows us to return the table. Am I right or wrong??

3) Multi-statement table valued UDF-
Can somebody tell me the definition of it.

create table studentp(roll int,name varchar,marks int,address varchar(10))
insert into studentp values(1,'A',10,'FBD')
insert into studentp values(2,'B',20,'Delhi')
insert into studentp values(3,'C',30,'Gurgaon')
select * from studentp

create function info2(@roll int)Returns @myTable TABLE
(
roll int,
name varchar,
address varchar(10)
)
AS
BEGIN
INSERT @myTable
Select roll,name,address
FROM studentp
RETURN
END

SELECT * FROM dbo.info2(1)

When I m calling the function it shows all the rows of the table,but i m passing roll as parameter to the function & i m passing roll Number 1,function has to showed the result of only Roll Number 1 ,then why all the rows are returned.
Apr 16 '08 #1
Share this Question
Share on Google+
1 Reply


P: 44
Three types of Functions are there-
1)Simple Scalar function-It returns a scalar value.

2)Inline-Table Valued UDF-
It allows us to return the table. Am I right or wrong??

3) Multi-statement table valued UDF-
Can somebody tell me the definition of it.

create table studentp(roll int,name varchar,marks int,address varchar(10))
insert into studentp values(1,'A',10,'FBD')
insert into studentp values(2,'B',20,'Delhi')
insert into studentp values(3,'C',30,'Gurgaon')
select * from studentp

create function info2(@roll int)Returns @myTable TABLE
(
roll int,
name varchar,
address varchar(10)
)
AS
BEGIN
INSERT @myTable
Select roll,name,address
FROM studentp
RETURN
END

SELECT * FROM dbo.info2(1)

When I m calling the function it shows all the rows of the table,but i m passing roll as parameter to the function & i m passing roll Number 1,function has to showed the result of only Roll Number 1 ,then why all the rows are returned.
You should use where condition for this :)

INSERT @myTable
Select roll,name,address
FROM studentp
WHERE roll = @roll

Better you verify yourself the questions you ask before posting !
Apr 16 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.