Set a computed column to a table valued function? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Set a computed column to a table valued function?

Author

Topic

weitzhandler
Yak Posting Veteran

64 Posts

Posted - 2009-03-09 : 03:25:07

Function selects all the phones that its ContactId=@ContactId (func. parameter).

now I want to create a computed col in the contact that returns all its phones using this function, possible?

Shimmy

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 03:39:30

As a table NO .. as a comma delimited list or XML list yes...

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

weitzhandler
Yak Posting Veteran

64 Posts

Posted - 2009-03-09 : 03:57:32

Hi! Thanks for your post!

Actually
I don't even mind to get the value as a scalar string, but it's complicated, here's the story:

each contact has few phones in the phone table assigned by the Phone.CcontactID col
besides the phone table has a SortOrder col (int) that speifies the order position of current entry between the other phones that belong to same contact.

Now I actually want this function to return a string that consists of all the phones for this contact ordered by sortorder col(separated by char(13) for line break):

select title, --this is the phone number itself
phonetype --this is an int that specifies whther the phone is a fax, cell etc., 1=phone, 2=cell etc.
from phone
where contactid=@contactid
order by sortorder

Am I clear somehow?
Why can't SQL be OOP!??? I am going mad

Shimmy

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 04:07:07

Heh .. You can do this ..

SELECT STUFF((SELECT ','+ Title
FROM Phone
WHERE ContactID=@ContactID
ORDER BY PhoneType
FOR XML PATH('')),1,1,'') AS 'PhoneList'

This will return a comma delimited list by your SortKey of phone numbers based on a contact id. If you want it to be XML then you can simply use ..

SELECT Title
FROM Phone
WHERE ContactID=@ContactID
ORDER BY PhoneType
FOR XML

Thanks...

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

weitzhandler
Yak Posting Veteran

64 Posts

Posted - 2009-03-09 : 15:04:13

quote:
Originally posted by guptam
This will return a comma delimited list by your SortKey of phone numbers based on a contact id. If you want it to be XML then you can simply use ..

SELECT Title
FROM Phone
WHERE ContactID=@ContactID
ORDER BY PhoneType
FOR XML

Thank you very much for your post, I think that's what I am dactually looking for, but it doesn't work, I tried simple query in a window (just your query) and I get the following msg:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'XML'..

I think this is the best solution to use XML, cuz then I can easily parse it with linq.

Shimmy

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 15:08:08

Blah LOL Should check my syntax before posting it .. thats what I get for messing around on forms at 4AM LOL

Try ...

SELECT Title
FROM Phone
WHERE ContactID=@ContactID
ORDER BY PhoneType
FOR XML PATH('')

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

weitzhandler
Yak Posting Veteran

64 Posts

Posted - 2009-03-09 : 16:56:55

Well, now the query itself works but im unable to use it in the function:

ALTER FUNCTION Getphones
(
	@ContactId   int 
)
RETURNS XML
AS
BEGIN
	DECLARE  @Result  xml
	SELECT   @Result  = (
SELECT Title, PhoneType 
FROM Phone
WHERE Contact = @ContactId
ORDER BY SortOrder
FOR XML PATH('')
)

	RETURN   @Result 
END
GO

Error message:
Msg 3729, Level 16, State 3, Procedure Getphones, Line 17
Cannot ALTER 'Getphones' because it is being referenced by object 'Contact'.

Shimmy

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 17:08:34

You must already have the computed column defined in the table? Drop column, alter function and re-add column column. I would also recommend *if* storage is not an issue for you to make the computed column "PERSISTED". So your system does not get burden with recomputing results every time someone does a select from the contacts table. Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

weitzhandler
Yak Posting Veteran

64 Posts

Posted - 2009-03-09 : 18:00:25

Oh, sorry, my mistake, what a dumb I am, I didn't even read the err message, it's because I already set the computed column

lol

thank you so much for help!

Shimmy

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 18:19:25

Heh we all get caught sleeping like I did at 4am ;-). I was hmm zombie posting! ^_^

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

weitzhandler
Yak Posting Veteran

64 Posts

Posted - 2009-03-12 : 10:48:59

Actually, I hae a problem, I want to wrap all the element in a root element and set the values to the attributes
example:

<Phones>
    <Phone PhoneType="1" Title="1 234 567 8910" />
    <Phone PhoneType="1" Title="1 234 567 8910" />
</Phones>

Shimmy

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-12 : 10:57:01

Change last line to "FOR XML RAW('Phone'), ROOT('Phone')"

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources