March 2, 2011 at 1:39 pm
create function fndata(@firstname varchar(100))
returns
@vool table(
FirstName varchar(100),
Middlename varchar(100),
LastName varchar(100),
ContactId int primary key clustered,
SalesorderId int,
TotalDue money
)
begin
insert into @vool (FirstName, Middlename, LastName, ContactId, SalesorderId,TotalDue)
select a.FirstName, a.Middlename, a.LastName, a.ContactId,b.SalesorderId,b.TotalDue from Person.Contact a
inner join Sales.SalesOrderHeader b on a.ContactID=b.ContactID
where FirstName like @FirstName
declare @cont int
select @cont=COUNT(*) from @vool
if @cont=0
insert @vool(FirstName,Middlename,LastName,ContactId,SalesorderId,TotalDue)
values ('','','','','','')
return
end
Here (FirstName,Middlename,LastName,ContactId) belong to Person.Contac table and other two belong to Sales.SalesOrderHeader)
if query function
select* from fndata('kim') it should return data related to kim.
plz help me to solve this pbm. i am getting error not able solve
March 2, 2011 at 2:07 pm
This looks like homework 😉
I'll give you a hint though, refer to the error message and then look at the definition for your table named @vool, it's PRIMARY KEY and the query being used to populate that table within your function.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 2, 2011 at 9:07 pm
solved thanks
March 4, 2011 at 6:46 am
1) TVFs that have multiple statements like this are HORRIBLE from a performance standpoint.
2) the clustered PK on the table will probably induce a lot of overhead for absolutely no gain
3) testing @@rowcount after the initial insert instead of counting all the rows would be a much more efficient construct to determine if you need the empty row inserted. Suppose a million rows match your insert query - you will have to COUNT THEM ALL just to see that the table var is non-empty. Bad news there.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply