Function - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Function

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2006-01-30 : 15:38:40

I have the following function.
It is working fine however it's slow.
Is it possible to speed it up?

CREATE FUNCTION dbo.viewAnswers
(@TranID decimal,@groupID varchar(10) )
RETURNS varchar(8000)
AS
BEGIN
Declare @Notes varchar(8000)
Set @Notes=''
Select @Notes=@Notes + Case Answer when 'Yes' then 'Y' else 'N' end + ' '
From answers
Where GroupID=@groupID and tranid=@TRANID
Return @Notes
END

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-30 : 15:47:14

Why isn't this a stored procedure?

Read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-30 : 15:54:21

This may be a little faster:

CREATE FUNCTION dbo.viewAnswers
(@TranID decimal,@groupID varchar(10) ) 
RETURNS varchar(8000)
AS
BEGIN
Declare @Notes varchar(8000)
Set @Notes=''
Select @Notes=@Notes + Cast(Answer as char(1)) + ' '
From answers 
Where GroupID=@groupID and tranid=@TRANID
Return @Notes
END

Is your original code all that slow? How much data are you working with, and is it well indexed?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-30 : 15:55:43

How do you know which character in the return value refers to which row in the answers table?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-30 : 16:45:19

Good point. He at least needs and ORDER BY QUESTIONID clause.

sardinka
Posting Yak Master

142 Posts

Posted - 2006-01-31 : 08:23:13