Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2006-06-06 : 09:20:25
|
peter writes "Hard question I guess. Inside my user-defined function (call it my_code) is a code that generates a temporary table based on some data. Function will return that table so that it can be used in 'select ___ from' query. Problem is the number and names of columns of that temp table depend on the data. So they change. Inline table-valued function is no good, because it can contain any code to generate a temp table. Multistatement table-valued function is no good, because I need to define columns in returns statement. How do I solve this problem? Doesn't have to be a function at all. Anything. Has to be ONE piece of code, and have to be able to use it in SELECT query, e.g. select * from my_code where... Thanks guys." |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-06 : 09:34:17
|
Is it that you must use table function ? Why not use do it in a stored procedure ? KH |
|
|
pl80
Starting Member
8 Posts |
Posted - 2006-06-08 : 14:08:30
|
I don't have to use a function, but I can't use a stored procedure, because I can't say: select * from my_procedure where..., and that is what I need. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-06-09 : 10:22:21
|
Use storted procedure and copy the result to other temp tableCreate table #temp(cols........)Insert into #temp EXEC SPMadhivananFailing to plan is Planning to fail |
|
|
pl80
Starting Member
8 Posts |
Posted - 2006-06-09 : 10:46:01
|
This has to be one piece of code that creates temp tables and returns results. I also have to be able to say select * from this_code where... Again: this_code generates temp tables and returns results. |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-06-09 : 12:04:32
|
You are asking the server to allow you to say:"select whatever exists from a recordset of indeterminate structure".No can do. How do you expect the optimizer to deal with that, or join the results to another dataset?I'm suspecting your application design is too loose. |
|
|
pl80
Starting Member
8 Posts |
Posted - 2006-06-12 : 14:23:54
|
You can say select * from my_function and have that function generate temp tables and then return some record set from them if: it is a Multistatement table-valued function, and you know what columns it will return (in order to define the function). But what if columns depend on data and you don't know what they are? That's the issue here. How can I go around that? My requirements are strange, but so is my boss :) |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-12 : 20:45:16
|
quote: Originally posted by pl80 You can say select * from my_function and have that function generate temp tables and then return some record set from them if: it is a Multistatement table-valued function, and you know what columns it will return (in order to define the function). But what if columns depend on data and you don't know what they are? That's the issue here. How can I go around that? My requirements are strange, but so is my boss :)
You can't do that. The table structure that a function returns is defined at the time it is created.I really don't understand your requirements. Please explain what you are trying to accomplish in your application.CODO ERGO SUM |
|
|
pl80
Starting Member
8 Posts |
Posted - 2006-06-13 : 14:24:08
|
Ok, let me try to do a better job explaining.1). Imagine a table: table1 with following columns: PersonName, Question, Answer.Question could be: 'YourPhone', 'YourAddress', etc.Sample record: 'John Smith', 'YourAddress', '3 Main st'2). I want to get a result that will have the following columns: PersonName, YourPhone, YourAddress, etc ... a column for each question that will have answers from table1.Sample record: 'John Smith', '3 Main st', '132-456-7890'Above is easily doable in dynamic sql3). I want to be able to say: select * from BOX where name = 'John Smith' and get result in format described in part 2.4). BOX could be whatever, but it must contain code to generate the result which I then limit by using where clause. And it obviously must be stored, it cannot be a piece of code I will copy and paste.Feel free to ask further questions. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-06-13 : 15:22:25
|
What are you doing with these results? What is your presentation layer?It is much, much, much easier to worry about putting this all into 1 row in the presentation layer, not in the database. Return the results normally, and let your client do the formatting. This is how databases are supposed to work; returning or working with results where the column names vary from call to call is *not* a good idea. |
|
|
pl80
Starting Member
8 Posts |
Posted - 2006-06-14 : 14:15:29
|
The results will viewed in SQL (Query Analyzer). They will not be forwarded anywhere. There is no presentation layer per se. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-06-14 : 14:54:18
|
???In Query Analyzer, you should be focusing on the raw data itself, writing efficient SQL, calculating accurate formulas, and so on ... not whether or not things are all on 1 line or are formatted a certain way. It's really not designed for that; it is not a tool for writing reports. |
|
|
pl80
Starting Member
8 Posts |
Posted - 2006-06-15 : 13:35:26
|
We do data analysis, that's what this is for - to have a better idea about data, this is not for writing a report. Anyway, I'm not asking what query analyzer is for, I want to know whether and how can this be done in SQL only! And I'm asking it here because it's not easy, I didn't join this forum to ask how to do inner join, or select top 100. |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-06-15 : 13:58:47
|
Try checking out the link in my signature, and restating your question.Help us help YOU!Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
pl80
Starting Member
8 Posts |
Posted - 2006-06-16 : 10:23:51
|
I give up. I can't explain my problem any better. Must be my fault, since nobody can help me. Anyway, I'll try to find answer someplace else, but definitely will post it here once I have it. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-06-16 : 11:15:56
|
If you want to analyze you DATA, use MS Access, or Excel, or other data analysis tools. It's QUERY analyzer, not DATA analyzer. That's all I am trying to tell you. Focus on writing your SQL statements in QA, and once you have done that, use a tool like Excel or Access or Reporting Services to allow you to view and browse and analyze the data. What you want is something called "Pivoting". Excel has this built in and can do it very quickly and easily. The same with MS Access. My point is not to use the wrong tool for the job. It *is* possible to take a data that is normalized and transform it into random number of columns in T-SQL and to use dynamic SQL to process and pivot your data and produce a table or resultset with columns created based on what is stored, but it is sloppy, inefficient, and you often create huge dynamic SQL statements that run up against the 8000 char limit of a varchar() in T-SQL and you need to work around that as well. Isn't a 1 line SELECT a little easier and more efficient? Why jump through hoops to and try to hammer in nails with a screw driver when you should be using a hammer?I'm not suggesting that you should be lazy and not use QA for this; I am suggesting that you should be smart. |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-06-16 : 11:25:54
|
You are trying to write a single piece of code that will return different result schemas depending upon the input. Not only is that not allowed in a UDF (as was pointed out above, the result layout is determined at the time it is created), but it is a bad idea in the first place because true reporting tools (Crystal, Access, even Excel...) depend upon knowing the format of the data they are going to receive. If the layout changes from one execution to the next, then the output is practically useless for any user interface. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-16 : 11:31:17
|
I read through his posts several times, and I have to say I can't begin to understand what he is after.He didn't seem too willing to try explain it, either, or to post some examples of what his expected output looks like.It's tough to program something if you can't even write a coherent explanation of what you are after.CODO ERGO SUM |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-06-16 : 13:02:05
|
I think he was just suffering from being a bit "too creative". I give him kudos for trying to think outside the box, but he really needs to use the right tool for the right job. |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-06-21 : 11:12:13
|
What right tool? Hammer is the only tool needed. If it doesn't work, your hammer isn't big enough.</sarcasm>Help us help YOU!Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-06-21 : 11:58:10
|
I have to give credit to Rob Volk for that analogy, it is one of his classics. That and the one about using a tweezer to move sand from one jar to the next, as opposed to just dumping it all in (cursors versus set based). |
|
|
Previous Page&nsp;
Next Page
|