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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 table-valued functions

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

Go to Top of Page

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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-09 : 10:22:21
Use storted procedure and copy the result to other temp table

Create table #temp(cols........)

Insert into #temp EXEC SP

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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 :)
Go to Top of Page

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
Go to Top of Page

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 sql

3). 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.
Go to Top of Page

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.

Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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)
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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)
Go to Top of Page

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).

Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -