User Defined Function: Convert String value of Table to Table Object

[Erik Grob \(MCP\)]

Does anyone know where to find or how to write a quick user defined fucntion
that will return a table object when passed the string name of the table
object. The reason why I want dynamicallly set the table name in a stored
procudue WITHOUT using concatination and exec a SQL String.

Hence

If @small_int_parameter_previous = 1 then
@vchar_tablename = "sales_previous"
else
@vchar_tablename = "sales"
End

select * from udf_TableLookup(@vchar_tablename )

So if I pass 1, that means I want all records from "sales_previous"
otherwise give me all records from "sales" (Sales_Previous would last years
sales data for example).

udf_TableLookup would I guess lookup in sysobjects for the table name and
return the table object? I don't know how to do this.

I want to do this to avoid having 2 stored procedures..one for current and
one for previous year.

Please respond to group so others may benfiit from you knowledge.

Thanks

Erik

[Ross Presser]

On Tue, 17 Aug 2004 08:51:44 -0700, Erik Grob (MCP) wrote:

Does anyone know where to find or how to write a quick user defined fucntion
that will return a table object when passed the string name of the table
object. The reason why I want dynamicallly set the table name in a stored
procudue WITHOUT using concatination and exec a SQL String.

Hence

If @small_int_parameter_previous = 1 then
@vchar_tablename = "sales_previous"
else
@vchar_tablename = "sales"
End

select * from udf_TableLookup(@vchar_tablename )

So if I pass 1, that means I want all records from "sales_previous"
otherwise give me all records from "sales" (Sales_Previous would last years
sales data for example).

udf_TableLookup would I guess lookup in sysobjects for the table name and
return the table object? I don't know how to do this.

I want to do this to avoid having 2 stored procedures..one for current and
one for previous year.

Please respond to group so others may benfiit from you knowledge.

Thanks

Erik

Others may know better than I, but I don't think you can make this
completely general.

The table structure returned by your UDF has to be the same no matter which
table gets selected from. So right there, you have a limitation - you
can't return *ANY* table.

Then, there's no "table object" that can be returned. You have to use a
select, no matter what you do.

You could use a construct like this:

create function udf_TableLookup( @tablename varchar(20) )
returns @retTable table (
salesDate datetime,
salesman varchar(10),
saleAmount money(6,2)
)
AS
BEGIN
IF @tablename = 'sales' then
INSERT @retTable
SELECT * from SALES
ELSE
IF @tablename = 'sales_previous' then
INSERT @retTable
SELECT * from SALES_PREVIOUS
ELSE
IF @tablename = 'sales_ancient' then
INSERT @retTable
SELECT * from SALES_ANCIENT
END

That would free you from multiple stored procedures...

[Erik Grob]

Thanks! You are correct, I have this user defined function that is great
and I thought I could do something with:
CREATE FUNCTION fnSplitIntoTable (@InString VARCHAR(4000), @DELIMITER
CHAR(1))
RETURNS @OutTable TABLE (ValuesList VARCHAR(255))
AS
BEGIN
DECLARE @pos INT
SET @InString = RTRIM(LTRIM(@InString))
SET @pos = CHARINDEX(@DELIMITER,@InString,1)
WHILE @pos <> 0
BEGIN
INSERT INTO @OutTable
SELECT LTRIM(RTRIM(LEFT(@InString,@pos-1)))
SET @InString = SUBSTRING(@InString,@pos+1,LEN(@InString))
SET @pos = CHARINDEX(@DELIMITER,@InString,1)
END
INSERT INTO @OutTable
SELECT @InString
RETURN
END
but you're right when you return a TABLE you have to give the structure I
guess, so the udFunction would actually have to popuate and ENTIRE (temp)
table and return it... YUCK!!!!