Table valued function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table valued function

Author

Topic

mahimam_2004
Starting Member

40 Posts

Posted - 2007-03-31 : 13:06:57

Hi,

I have one stored procedure which has the parameters in the following format

create procedure GetSpecies

@species varchar(100)='1,2,3,4,-1' here -1 is for null value selected from the report.

@Gender varchar(100)='2,3,4,-1'

as

Select .. from

cacase c

Left join Patient p on p.caCaseid=c.caCaseid

some joins here

Where p.speciesID in(Select * from dbo.getCSV(@Species))

And p.GenderID in(Select * from dbo.getCSV(@Gender))

Where as GetCSV is a table valued function which takes the Comma separated string and didvided it into individual parts and insert into table from this TBV I am returning the table which I am using in the Where clause of the above select statement.

Here my question is if we call the GetCSV tbv in the above where clause it will be called for each SpeciesID of the Patient table.If PaPatient table has 1000 records it will be called that 1000 time i think.

Can we do like this before the above Select statement.

Declare @t table

Select @tSpec= Select * from dbo.GetCSV(@Species)

and use this @TSpec in the where clause of the above select statement.

same for Gender also.

Thanks in advance.

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2007-03-31 : 18:03:10

Yes. This will be fine. Even you can get rid of function if you use a dynamic query to insert the table variable and use the table variable to join with the respective table.

Work smarter not harder take control of your life be a super achiever

mahimam_2004
Starting Member

40 Posts

Posted - 2007-03-31 : 19:19:48

Select @tSpec= Select * from dbo.GetCSV(@Species)

This is not working this is giving the error.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-03-31 : 21:50:29

I think you are mistaken about the function being called once for each SpeciesID in the patient table. View the query plan to see.
Regardless, you can treat table functions like any function or view, so try this syntax instead:

Select	..
from	cacase
	Left join Patient on Patient.caCaseid=cacase.caCaseid
	...
	inner join dbo.getCSV(@Species) Species on Patient.SpeciesID = Species.[ColumnName]
	inner join from dbo.getCSV(@Gender) Genders on Patient.GenderID = Genders.[ColumnName]

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources