Returning table from UDF

Thank you David
My problem is the following:
I have certain tables to which apart from a standard number of fields
additional fields are added depending on customer requirements. A "Select *
From Table_A" statement returns all fields but because there are several
versions of the same record in the table some quite elaborate filtering
(based on current criteria) is needed to get a single record. This results
in cubersome queries. So I was wondering if I could write a UDF which would
"hide" all the complication and call it as "Select * From
UDF_A(param1,param2,..)"?
What I already have done is write a UDF returning as a table the Ids of the
records satisfying the criteria and then using that to filter the original
table ie
"Select * From Table_A where Table_A.Id IN (Select Id From UDF_X(params))"
but it is still a litle complicated for "report writers" who don't know the
internals.
Yannis
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:nbidnZWNSYMQ_2zcRVn-jA@.giganews.com...
> No. Stored procedures can return variable numbers of columns though.
That's
> not to say that dynamic result sets from an SP is necessarily a good way
to
> accomplish things. Maybe if you describe your problem we can suggest a
> solution.
> --
> David Portas
> SQL Server MVP
> --
>A UDF has only one returned set. If varying return sets are needed,
multiple UDF's will have to be written. If additional fields are added to
the table in the future, those fields will have to be added to any UDF's for
the fields to appear in their return sets.
If you have a very limited param set, you can get away with making a view
for each one:
CREATE VIEW View_1 AS Select * From Table_A where Table_A.Id IN (Select Id
From UDF_X(1))
CREATE VIEW View_2 AS Select * From Table_A where Table_A.Id IN (Select Id
From UDF_X(2))
CREATE VIEW View_3 AS Select * From Table_A where Table_A.Id IN (Select Id
From UDF_X(3))
These views won't need modifying as the fields are added. They are very
simple for your report writers:
SELECT * FROM View_2
If you don't have a limited param set, you could try stored proc's...
CREATE PROC ShowTableA @.param1 int, @.param2 varchar(10)
AS
Select * From Table_A where Table_A.Id IN (Select Id From UDF_X(@.param1,
@.param2))
RETURN
Called by:
EXEC ShowTableA 22,'blue'
The downside here is that to capture the result in (for example) query
analyzer, you have to jump through some hoops (one of the hoops is knowing
the number and datatypes of the columns in the returned set)... maybe your
report writers are using some other tool where they don't issue sql against
the result set to do further modification? If so, this is a good choice.
"Y.Makarounis" <ymak@.otenet.gr> wrote in message
news:ONWYyJGAFHA.1908@.TK2MSFTNGP15.phx.gbl...
> Thank you David
> My problem is the following:
> I have certain tables to which apart from a standard number of fields
> additional fields are added depending on customer requirements. A "Select
*
> From Table_A" statement returns all fields but because there are
several
> versions of the same record in the table some quite elaborate filtering
> (based on current criteria) is needed to get a single record. This results
> in cubersome queries. So I was wondering if I could write a UDF which
would
> "hide" all the complication and call it as "Select * From
> UDF_A(param1,param2,..)"?
> What I already have done is write a UDF returning as a table the Ids of
the
> records satisfying the criteria and then using that to filter the original
> table ie
> "Select * From Table_A where Table_A.Id IN (Select Id From
UDF_X(params))"
> but it is still a litle complicated for "report writers" who don't know
the
> internals.
> Yannis
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:nbidnZWNSYMQ_2zcRVn-jA@.giganews.com...
> That's
> to
>