Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lrdgrd
Starting Member
5 Posts |
 Posted - 2006-03-09 : 10:04:18
|
| Hi there,I am trying to create a UDF to retun the value of the tables/views wich name is the parameter i am recieving.I want to do this to avoid making hundres of views with simple queries in them.My idea si to so something similar to this :CREATE FUNCTION name ( @dbname varchar(50))RETURNS TABLEASSELECT [col] FROM @dbnameRETURN i cant find a way to link the table name with the parameter i get.Is there someone who has done this before that could give me a hand on this ? |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-09 : 10:09:44
|
| UDF's cannot execute a dynamic query. When table names are variable from SQL statement to the next, that usually indicates a poor database design; i.e., data spread out in multiple tables that should be all in 1 place, or too many "super-stored procs" that are doing too much at once. Why do you need to do this? |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-09 : 10:10:17
|
| You can't do this in Function. You have to use stored procedure with Dynamic SQL----------------------------------'KH' |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-09 : 10:15:31
|
| With dynamic SQL,EXEC('Select col from '+@table)MadhivananFailing to plan is Planning to fail |
|
|
|
lrdgrd
Starting Member
5 Posts |
Posted - 2006-03-09 : 10:17:12
|
| I need this because the database has hundres of views. Each represents 1 tag in which you have a timestamp, a value and a quality status. This is for history data management from live industries.I am doing a repport in wich i have to get part of the data from several diferent tables, but once at a time.My idea was to make a unique function where i give the view name as a parameter and that function would give back the values im asking for that specific view. That would save me alot of time making several new views that will be manipulating the data from the original views in one simple function. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-09 : 11:03:05
|
| Hundreds of views? Your data should not determine the # of views you have, it should be your database model. Are you saying that if your database has 100 "tag" entities, then you have created 100 views for them? Why? Is this for security? |
|
|
|
lrdgrd
Starting Member
5 Posts |
Posted - 2006-03-09 : 11:15:16
|
| this is the way the original company made it. i am just working on their work and cant change that.Anyways, i managed to do it with the store procedure with the dynamic sql.Tnx for guiding me to that option. |
|
|
|
|
|
|
|
|
User Defined Function, dinamic table calling