August 11, 2004 at 10:52 am
I am attempting to write a UDF that reads from INFORMATION_SCHEMA.COLUMNS and then based on a given TABLE_NAME, creates a new table datatype that mimics the structure of the aforementioned table. my first guess was to implement a scalar UDF and return a single value of type table.
I declared my UDF as:
CREATE FUNCTION udf_clone_table
(@TableName sysname)
RETURNS table
AS
BEGIN
DECLARE @TableClone table
DECLARE @ORDINAL_POSITION int
DECLARE @COLUMN_NAME nvarchar(128)
DECLARE @DATA_TYPE nvarchar(128)
DECLARE @CHARACTER_MAXIMUM_LENGTH smallint
SET @ORDINAL_POSITION = (SELECT MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName)
WHILE @ORDINAL_POSITION IS NOT NULL
BEGIN
SELECT
@COLUMN_NAME = COLUMN_NAME,
@DATA_TYPE = DATA_TYPE,
@CHARACTER_MAXIMUM_LENGTH = CHARACTER_MAXIMUM_LENGTH
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @TableName AND
ORDINAL_POSITION = @ORDINAL_POSITION
IF @@ROWCOUNT > 0
BEGIN
DECLARE @stmt nvarchar(255)
-- do dynamic stuff here
END
-- goto next position
SET @ORDINAL_POSITION = (SELECT MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @ORDINAL_POSITION)
END
RETURN @TableClone
END
Now, the syntax check doesn't like this. First question is can I execute ALTER TABLE against a table data type? Is there a better way to do this?
August 12, 2004 at 5:44 am
Doing what you want isn't possible in a UDF. UDF's don't allow execution of dynamic SQL. The results set returned by the UDF must be declared when the UDF is created. You're best bet is a stored procedure. SPs can execute dynamic SQL and return a result set determined at run time.
For more on UDFs see my book Transact-SQL User-Deinfed Functions.
Regards,
Andy
August 12, 2004 at 1:09 pm
Why not just copy the table structure, without any data:
SELECT * INTO newtable FROM oldtable WHERE 1=2
Mike
August 12, 2004 at 1:58 pm
I don;t know really. I need to create a temp table to hold data for paging. I will skip x rows, move the next y rows into the table, and then return the contents of the temp table. I was trying to keep from having to explicitly declare my table datatype with all the necessary columns. I was hoping that I could create it programitically using schema metadata.
August 12, 2004 at 8:31 pm
The script below is part of a large batch script to create shadow tables (same field names and datatypes)
with additional fields. Embed part of this or similar code into a sp.
Win
---------------------------------------------------------------------------------------------
declare tab cursor fast_forward for SELECT o.name,c.name,c.colorder,c.xusertype,c.length
FROM sysobjects o, syscolumns c
where o.name like '<use a table filter>'
and o.xtype='U'
and o.id=c.id
order by o.name,c.colorder
open tab
fetch tab into @tabname,@fldname,@fldorder,@dtype,@fldlen
set @lcount=0
while @@fetch_status=0
begin
set @datatype=
case
when @dtype=48 then ' tinyint NULL,'
when @dtype=52 then ' smallint NULL,'
when @dtype=56 then ' int NULL,'
when @dtype=58 then ' smalldatetime NULL,'
when @dtype=61 then ' datetime NULL,'
when @dtype=62 then ' float NULL,'
when @dtype=106 and @fldlen=5 then ' decimal(7,4) NULL,'
when @dtype=106 and @fldlen=9 then ' decimal(10,4) NULL,'
when @dtype=167 then ' varchar(' + cast(@fldlen as varchar) + ') NULL,'
when @dtype=175 then ' char(' + cast(@fldlen as varchar) + ') NULL,'
else 'UNKNOWN' --this datatype will trip the execute statement
end
--print @datatype
set @lcount=@lcount+1
if(@fldorder=1)
begin
if(@lcount=1)
set @stmt='create table [dbo].NEW_' + @tabname + ' (' + char(13) + @fldname + @datatype + char(13)
else
begin
set @stmt=@stmt + char(13) + '<append some extra fields/constraints to new table and close statement>'
--print @stmt
exec(@stmt)
set @stmt='create table [dbo].NEW_' + @tabname + ' (' + char(13) + @fldname + @datatype + char(13)
end
end
else
set @stmt=@stmt + @fldname + @datatype + char(13)
fetch tab into @tabname,@fldname,@fldorder,@dtype,@fldlen
end
set @stmt=@stmt + char(13) + '<append some extra fields/constraints to new table and close statement>'
--print @stmt
exec(@stmt)
close tab
deallocate tab
August 12, 2004 at 9:26 pm
That looks great, but am I correct in saying that it creates an actual new table. I only need the table inside the sproc and then I'm done with it. I'd like to avoid using a #temptable and make use of a table datatype. looks like that isn't going to happen.
August 12, 2004 at 11:03 pm
Why don't you create the datatype table following the same principle and stick with the information_schema views which are more up-to-date than the old hack of using sys...tables.
Win
August 12, 2004 at 11:27 pm
You can use temp tables to test your design if up-front re-coding looks like too much work. Temp tables created inside a procedure are automatically destroyed when they go out of scope, ie the procedure is closed.
Win
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply