can we pass temp table as parameter into functions

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 2005 Forums

Transact-SQL (2005)

can we pass temp table as parameter into functions

Author

Topic

srinisql
Starting Member

9 Posts

Posted - 2009-04-28 : 07:04:36

Dear All,

Please help to clarfiy my doubt. can we pass temp table as as a parameter into the function and also is there any option to aggregate string values

like i have 1 table with the following records

author name -- book
---------------------
john c
john c++
john java
john fortran
jack html
jack struts
jack jsp

my result of the query shoud like this

author name book
------------------------------------
john c,c++,java,fortran
jack html,struts,jsp

for the above i can use, partition option but the problem i have 1000 of jack records which is not possible to give rank. so pls advise the flexible , dynamic and reliable solution for this

Thanks and advance,

S.Srinivasan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 07:09:54

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

srinisql
Starting Member

9 Posts

Posted - 2009-04-28 : 07:15:58

Thanks buddy for Response,

I have already used this option, but it takes long run to execute. is there any optimized pre defined function available

Thanks in advance

srinisql
Starting Member

9 Posts

Posted - 2009-04-28 : 07:18:01

When i used the above approach, i get a error like
A severe error occurred on the current command. The results, if any, should be discarded.

how to overcome

Thanks in Advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-28 : 07:20:00

quote:
is there any optimized pre defined function available

If there were, there'd be no need for a user-defined function to do it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 07:22:46

quote:
Originally posted by srinisql

When i used the above approach, i get a error like
A severe error occurred on the current command. The results, if any, should be discarded.

how to overcome

Thanks in Advance

No function is available as is in MYSQL (Group_concat function)

Madhivanan

Failing to plan is Planning to fail

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 07:27:43

[code]
DECLARE @Books TABLE
(
Author VARCHAR(50),
Name VARCHAR(50)
);
INSERT INTO @Books (Author,Name) VALUES('john','c');
INSERT INTO @Books (Author,Name) VALUES('john','c++');
INSERT INTO @Books (Author,Name) VALUES('john','java');
INSERT INTO @Books (Author,Name) VALUES('john','fortran');
INSERT INTO @Books (Author,Name) VALUES('jack','html');
INSERT INTO @Books (Author,Name) VALUES('jack','struts');
INSERT INTO @Books (Author,Name) VALUES('john','jsp');

SELECT Author, stuff(( SELECT ',' + [name] FROM @Books drv WHERE drv.Author = main.Author
FOR XML PATH('')), 1, 1, '') FROM @Books main GROUP BY Author; [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources