November 15, 2007 at 6:15 am
Hello Guys !
I have a table (lets name it mytable) like this
table_name Column_name Date Value
A Col1 10/10/2007
A Col2 10/11/2007
B Col1 10/10/2007
B Col3 10/10/2007
I have all the tables that are there in table_name and those tables contain the respective columns as well, e.g.
Table A
-----------------------------
Date col1 col2
10/10/2007 20 fur
10/11/2007 20 afur
10/12/2007 21 fffur
What i want is to write some function that takes the table_name,column_name and date and gives me the value of that particular column from that particular table for that date.
i want to use this function as a formula in "value" column of table "mytable" above.
Any idea??
what solution can be used???
November 15, 2007 at 7:15 am
you can use a t sql function to generate a value for a calulated column in a table. I've used the technique since sql 2000.
For those readers who don't know about such things:- you can create a calulated column on a table using T-SQL but you can't look up another table , however if you use a function then you can join or lookup against other tables. The downside is that this is a virtual column - in sql 2005 you can index the column and this will materialise the data in the column. ( with some restrictions )
For small non critical tables it's fine but on large tables the performance can be appalling so use with extreme care.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 15, 2007 at 9:55 pm
please give me an example. i tried but am unable to write such a function, but that not working.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create function [dbo].[udf_PAT_changes_value] (
@id smallint,@datee varchar(50),@colName varchar(100),@tableName varchar(50)
) Returns varchar(MAX)
AS BEGIN
DECLARE @theValue varchar(MAX)
exec('SELECT TOP 1
@theValue = '+@colName+' FROM' + @tableName + ' WHERE id = ' + @id
+ ' and date=' + @datee)
RETURN @theValue
END
Please help!!!
November 15, 2007 at 10:39 pm
please try with below querry
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create function [dbo].[udf_PAT_changes_value] (
@id smallint,@datee varchar(50),@colName varchar(100),@tableName varchar(50)
) Returns varchar(MAX)
AS BEGIN
DECLARE @@theValue varchar(MAX)
exec('SELECT TOP 1
@@theValue = '+@colName+' FROM' + @tableName + ' WHERE id = ' + @id
+ ' and date=' + @datee)
RETURN @theValue
END
November 18, 2007 at 9:45 pm
well. thats great problem that i have too short deadlines that i cant consult any book right now.
Anyways, i got it working, i did the CLR integration and that was awsome.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply