How to get this done - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

How to get this done

Author

Topic

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-10 : 02:19:32

Hi Folks,

Hope you're doing well.

Trying to resolve the following scenario with the help of Table Valued Function.

Three tables:

1) Books (BookID, BookName)
2) BookAuthor (pd_Id, BookID,AuthorId)
3) Authors (AuthorId, FirstName, SurName)

Need to get the output in the following form

BookID, FirstNames,SurNames,FullNames,AuthorIDs

Explanation of the FirstNames and SurNames format: if a Book has multiple Authors then the FirstNames should be Comma separated First Names of All Authors of that particular book. Same for the SurNames too.

FirstName_Author1+','+FirstName_Author2+','+...+','FirstName_AuthorN

Explanation of the FullNames format: Colon separated Surname,Firstname of all authors for that particular book. e.g.

SurName_Author1+','+FirstName_Author1 +':'+SurName_Author2+','+FirstName_Author2

Explanation of the AuthorIDs Format: Comma Separated of All authorIDs e.g.

AuthorID_Author1+','+AuthorID_Author2

for this i have Created a TV function, which works well enough if i use it as

Select * from dbo.getAuthorNames(123456) and displays the data in the following format

bookID FirstNames SurNames Names authorIDs

However when i try to use it in the stored procedure with Join e.g.

Select *
from books
Inner Join dbo.getAuthorNames(Books.BookID)Authors on Authors.BookId=Books.BookId

I get the following error

Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '.'.

I would appreciate if someone could direct me onto a right track

Cheers
MIK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-10 : 02:45:50

change the INNER JOIN to CROSS APPLY

KH
[spoiler]Time is always against us[/spoiler]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-10 : 02:54:45

Thanks KH, tried that too ..which still giving me error e.g.

SElect MB.MetaBook_ID,Authors.*
from tblMetabook MB
Cross Apply dbo.getAuthorNames(Mb.Metabook_ID)Authors

SElect MB.MetaBook_ID,Authors.*
from tblMetabook MB
Cross Apply dbo.getAuthorNames(Mb.Metabook_ID)Authors on Authors.BookId=MB.MetaBook_ID

Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'Apply'.

Seems i am Missing some basic rule of using Table Valued funcs.

Cheers
MIK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-10 : 02:57:35

What is the version of SQL Server you are using ?

KH
[spoiler]Time is always against us[/spoiler]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-10 : 03:02:07

hmmmm .. the database is in SQL 2000 instance

Cheers
MIK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-10 : 03:14:51

ok. APPLY is only available from SQL 2005 onwards.


from books
Inner Join dbo.getAuthorNames(Books.BookID) Authors

Basically the above is not allow unless you are using CROSS APPLY. For JOIN method, you can't pass a non constant into the table function

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-10 : 03:20:56

since you are using SQL 2000, you can't use CROSS APPLY or for xml path to form the CSV. The best option is to use a UDF to perform the concatenation
see
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

KH
[spoiler]Time is always against us[/spoiler]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-10 : 05:09:57

quote:
Originally posted by khtan

ok. APPLY is only available from SQL 2005 onwards.

Basically the above is not allow unless you are using CROSS APPLY. For JOIN method, you can't pass a non constant into the table function

Thanks for this information ...

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources