SQL Server - Question about using Multistatement Table-Valued Function

Asked By Cindy Parker

06-Sep-07 10:56 AM

I am trying to create/use a Multistatement Table-Valued Function but
I'm having a hard time wrapping my head around this.  I've searched
but can't seem to find the answers, so I have a feeling that either
it's something really obvious or I'm taking a totally wrong approach.

Here's what I'm trying to accomplish.  I need a function that will
create a set of ProgramCodes given 3 parameters.  I've created a
separate table to hold some of the combinations, but conditional logic
is needed so I can't just create a table and join to it.  And there
will always be multiple ProgramCodes returned given the three
paramaters.  Basically:

tblMain:  CompoundPrimaryKey (7 fields, believe it or not), Year,
Kind, Coverage
tblLookup:  Year, Coverage, ProgramCode

The three parameters are "@Year", "@Kind", and "@Coverage".  It works
something like this (in pseudocode):

If @Year = 1996 Then
Select ProgramCode from tblLookup where Year = @Year and Coverage
= @Coverage
Else If @Year = 1997 Then
If @Kind = X and @Coverage = Y Then
Do nothing...
Else
Select ProgramCode from tblLookup where Year = @Year and
Coverage = @Coverage
....
Else If @Year > 2000 Then
If @Kind = X and @Coverage = Z Then
Do nothing...
Else
Set @Year = 2000
Select ProgramCode from tblLookup where Year = @Year and
Coverage = @Coverage


I've created a function which works when I pass it specific values.
Since I want to be able to join this to tblMain I pass in all the
fields in tblMain's compound primary key so that they will be part of
the outputted table.  For example, this works:

SELECT *
FROM dbo.udf_ProgramCodes('12345678', '001', '024','191', 34, 33,
'001', '046', '1996')

But when I try to join it and pass in the values from tblMain it
doesn't work.  Like this:

SELECT *
FROM dbo.udf_ProgramCodes(p.OCCURANCE, p.LINE, p.ITEM, p.COVERAGE,
p.CLAIMNUM, p.CLAIMANT,
p.CAUSE, p.KIND, p.YEAR) f
INNER JOIN PXCLAIM p
ON f.OCCURANCE = p.OCCURANCE AND
f.LINE = p.LINE AND
f.ITEM = p.ITEM AND
f.COVERAGE = p.COVERAGE AND
f.CLAIMNUM = p.CLAIMNUM AND
f.CLAIMANT = p.CLAIMANT AND
f.CAUSE = p.CAUSE

But I get an error:  "Msg 170, Level 15, State 1, Line 2     Line 2:
Incorrect syntax near '.'."   I thought of reworking the function to
return just the set of ProgramCodes and using it in a correlated
subquery but I'm not sure if that will get me what I need either.

The final result should be, given this tblMain:

OCCURANCE  LINE  ITEM  COVERAGE  CLAIMNUM  CLAIMANT  CAUSE  KIND
YEAR
12345              001    022    191               30
30               123       021     1996
15455              004    019     034               2
1                203       1134   1997

the end table should look like this:

OCCURANCE  LINE  ITEM  COVERAGE  CLAIMNUM  CLAIMANT  CAUSE  KIND
YEAR   PROGRAMCODE
12345              001    022    191               30
30               123       021    1996    HCE
12345              001    022    191               30
30               123       021     1996    HCL
12345              001    022    191               30
30               123       021     1996    PV1E
12345              001    022    191               30
30               123       021     1996    LLL03
15455              004    019     034               2
1                203      1134    1997   HCE
15455              004    019     034               2
1                203      1134    1997   PV1E
15455              004    019     034               2
1                203      1134    1997   LPL1
15455              004    019     034               2
1                203      1134    1997   HCVL
15455              004    019     034               2
1                203      1134    1997   LLL04

I know this is complicated.  Thank you for bearing with me.  Any
assistance you can give me, either suggestions as to how to make this
work or different approaches to try would be appreciated.  I am pretty
good with T-SQL but this is new territory for me and it has me
stumped.

Thank you.

- Cindy Parker

06-Sep-07 11:24 AM

Hi Cindy,

If you are using SS 2000, forget about it. For 2005, check CROSS APPLY and
OUTER APPLY in BOL.


FROM
dbo.udf_ProgramCodes(p.OCCURANCE, p.LINE, p.ITEM, p.COVERAGE, p.CLAIMNUM,
p.CLAIMANT, p.CAUSE, p.KIND, p.YEAR) f
cross apply
PXCLAIM p


AMB

06-Sep-07 11:25 AM

I think what you should look into (assuming 2005) is the APPLY operator, for instance

SELECT ...
FROM tbl1
CROSS APPLY fn1(tbl1.col1, tbl1.col2)
...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

06-Sep-07 11:39 AM

Thank you.  Yes, looking at BOL, CROSS APPLY is exactly what I would
like to do.  We have a mixed environment.  tblMain is on a SQL 2000
server, but I could create the function and the query on a SQL 2005
server.  Would that work?  Or does everything have to be SQL 2005?

- C

On Sep 6, 11:24 am, Alejandro Mesa

06-Sep-07 02:00 PM

Actually, this totally solved my problem.  And yes, I can do the
function and query on a 2005 server, with a linked 2000 server, and it
works.  Brilliant.  Thank you both.
- C

06-Sep-07 02:10 PM

Cindy,

I haven't try referencing the table from a 2000 instance.

If the query is in the 2005 side (db compatibility level = 90), referencing
a table in a 2000 database (linked server for 2000 instance or db
compatibility level = 80 for 2005 instance), I guess it will work.


AMB