October 26, 2007 at 4:37 pm
I have a UDF that is supposed to loop through a comma delimited list, grab the integer value out of that list, run a query on the database to get a string value, assign that string to a variable that will get appended as the loop runs and finally spit out the string. The problem I am having is that the UDF is displaying null. What am I doing wrong? Any help is appreciated
Here is the UDF
CREATE FUNCTION udf_GetCheckboxAnswers (@QuestionID AS int, @AnswerList as varchar(400))
RETURNS varchar(400)
AS
BEGIN
DECLARE @LocComma int
DECLARE @CurrentPos int
DECLARE @Answer varchar(400)
DECLARE @TempAnswer varchar(400)
DECLARE @ResponseID int
SET @LocComma = 0
IF LEN(@AnswerList) > 0
BEGIN
SET @LocComma = 1
SET @CurrentPos = 0
END
WHILE @LocComma > 0
BEGIN
SET @LocComma = CHARINDEX(',', @AnswerList)
IF @LocComma > 0
BEGIN
SET @ResponseID = SUBSTRING(@AnswerList, @CurrentPos, @LocComma)
SET @AnswerList = SUBSTRING(@AnswerList, @LocComma + 1, Len(@AnswerList))
SELECT
@TempAnswer = AnswerText
FROM
PrescreenResponses
WHERE
QuestionID = @QuestionID AND
AnswerValue = @ResponseID
SET @Answer = @Answer + ', ' + @TempAnswer
SET @AnswerList = LTRIM(@AnswerList)
END
ELSE
BEGIN
SET @ResponseID = @AnswerList
SELECT
@TempAnswer = AnswerText
FROM
PrescreenResponses
WHERE
QuestionID = @QuestionID AND
AnswerValue = @ResponseID
SET @Answer = @Answer + ', ' + @TempAnswer
END
END
RETURN (@Answer)
END
Changing the UDF so that it looks like the code shown below returns the very last value so I know I must be close
CREATE FUNCTION udf_GetCheckboxAnswers (@QuestionID AS int, @AnswerList as varchar(400))
RETURNS varchar(400)
AS
BEGIN
DECLARE @LocComma int
DECLARE @CurrentPos int
DECLARE @Answer varchar(400)
DECLARE @TempAnswer varchar(400)
DECLARE @ResponseID int
SET @LocComma = 0
IF LEN(@AnswerList) > 0
BEGIN
SET @LocComma = 1
SET @CurrentPos = 0
END
WHILE @LocComma > 0
BEGIN
SET @LocComma = CHARINDEX(',', @AnswerList)
IF @LocComma > 0
BEGIN
SET @ResponseID = SUBSTRING(@AnswerList, @CurrentPos, @LocComma)
SET @AnswerList = SUBSTRING(@AnswerList, @LocComma + 1, Len(@AnswerList))
SELECT
@TempAnswer = AnswerText
FROM
PrescreenResponses
WHERE
QuestionID = @QuestionID AND
AnswerValue = @ResponseID
SET @Answer = @Answer + ', ' + @TempAnswer
SET @AnswerList = LTRIM(@AnswerList)
END
ELSE
BEGIN
SET @ResponseID = @AnswerList
SELECT
@Answer = AnswerText
FROM
PrescreenResponses
WHERE
QuestionID = @QuestionID AND
AnswerValue = @ResponseID
END
END
RETURN (@Answer)
END
October 26, 2007 at 5:10 pm
Can you provide an example of input, walk us thru the intended logic and what the output should be like? Will make it easier to understand your overall goal.
October 27, 2007 at 3:47 am
I think the problem is that you have not initialized any value for the variable @Answer which defaults to NULL .....
--Ramesh
October 27, 2007 at 6:59 am
The input would look like this
SELECT udf_GetCheckboxAnswers (218, '2, 4, 5') AS AnswerString
The intended output in this example would be
Tuesday, Thursday, Friday
The second one I showed outputs Friday. Whearas the first outputs NULL
This function is called inside a SPROC that is used to display how an individual answered certain questions. The SPROC looks at 3 possible types of questions.
1 - single choice (radio button)
2 - essay (text input)
3 - multiple choice (checkboxes)
The SPROC looks at the type of question it is (this is determined by an integer value) then dependant on the value it associates the individuals answer with the questions. Since I am storing the multiple choice answers as integers and not strings, I need to look up the associated text that goes with that particular answer and then display that answer to the screen not the numerical value of it. Everything is working as it should with the exception of my UDF.
October 27, 2007 at 7:13 am
Miranda Johnson (10/27/2007)
The input would look like thisSELECT udf_GetCheckboxAnswers (218, '2, 4, 5') AS AnswerString
The intended output in this example would be
Tuesday, Thursday, Friday
The second one I showed outputs Friday. Whearas the first outputs null
Have you added the assignment SET @Answer = '' in the function?
--Ramesh
October 27, 2007 at 7:27 am
Yes that did not make any changes to the output
October 27, 2007 at 7:50 am
Will the values in @AnswerList always be available in PrescreenResponses table?
If no, then you have to change this SET @Answer = @Answer + ', ' + @TempAnswer
assignment statement to SET @Answer = @Answer + ', ' + COALESCE( @TempAnswer, '' ), otherwise i don't see why it is not functioning as expected???
--Ramesh
October 27, 2007 at 8:37 am
Just a personal opinion...
I think you may be trying to do too much in a UDF. I think the UDF should do nothing but find the number you're looking for and then find/append the other stuff to the results of the UDF in the query that uses the function.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2007 at 8:39 am
Yes, the answers are always available. the display of the questionairre is built from those tables. I made a change to the way we are storing the answers when a user clicks on a checkbox. Instead of storing the integer value associated with the selected response(s), I am now saving the text string. But I am still curious as to why my UDF didn't work.
October 27, 2007 at 9:09 am
But I am still curious as to why my UDF didn't work.
Your UDF works... but think about it... what do you get when you concatenate a NULL with anything? Check the data... and check Ramesh's suggestion.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply