asked on

Dynamically replace part of SELECT statement in UDF

I am trying to create a User Defined Function in SQL Server 2005.

I have a table Tbl_Data_Call_Variables. The fields are:
Apps_Cat_ID
Q01_Data
Q02_Data
Q03_Data
v
v
Q60_Data

The UDF needs to take in an Apps_Cat_ID and an initial text string with a place holder. The place holder will be in the format $$Q02_Data$$.

I need to dynamically modify a SELECT statement as part of the UDF so that it will process as follows:
@AppsCatID is an incoming parameter with an acceptable numeric value in it.
@pQuestionTemplate is a character string, such as: "This application has $$Q02Data$$ inputs."

The place holder, in this case $$Q02_Data$$, is looked up in the database. If it is available, and has a value of, say, 5, the incoming pQuestionTemplate is modified to be:
This application has 5 inputs. ....and returned.
If, on the other hand, the field in the database is null, the incoming pQuestionTemplate is modified to be:
This application has [Data missing] inputs. ....and returned.

The problem is that the place holder can be anything from $$Q01_Data to $$Q60_Data, and it is not predictable.

I know I could do 60 sequential replace statements, but that would require 60 statements. I would rather determine dynamically which place holder is being use and execute a lot less statements.

This is the statement I am having trouble with:
SET @modifiedQuestion = REPLACE(@pQuestionTemplate, @whichPlaceHolder, isnull((SELECT @whichField FROM Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))

How do I dynamically replace the @whichfield with the right value?

In my example above, the actual executed statement would be:
SET @modifiedQuestion = REPLACE(@pQuestionTemplate, @whichPlaceHolder, isnull((SELECT Q02_Data FROM Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))

CREATE FUNCTION [dbo].[Substitute_Data_Variable] 
	(
	@pAppCatID INT,
	@pQuestionTemplate VARCHAR(400)
	)
	RETURNS VARCHAR(4000)
AS
 
BEGIN
	DECLARE @modifiedQuestion     VARCHAR(4000)
	DECLARE @whichPlaceHolder varchar(4000)
	DECLARE @whichField varchar(4000)
		
	SET @whichPlaceHolder = SUBSTRING(@pQuestionTemplate,CHARINDEX ( '$$' ,@pQuestionTemplate , 1 ),12)
	SET @whichField = 	SUBSTRING(@whichPlaceHolder,3,8)
 
	SET @modifiedQuestion = REPLACE(@pQuestionTemplate, @whichPlaceHolder, isnull((SELECT @whichField FROM Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
 
	RETURN @modifiedQuestion
END

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

sorry AngelIII - must have had this window open for longer than I thought....

Here is a SP for atonement...

CREATE PROCEDURE [dbo].[SP_Substitute_Data_Variable] 
	(
	@pAppCatID INT,
	@pQuestionTemplate VARCHAR(400)
	)
AS
 
BEGIN
	DECLARE @modifiedQuestion     VARCHAR(4000)
	DECLARE @whichPlaceHolder varchar(4000)
	DECLARE @whichField varchar(4000)
		
	SET @whichPlaceHolder = SUBSTRING(@pQuestionTemplate,CHARINDEX ( '$$' ,@pQuestionTemplate , 1 ),12)
	SET @whichField = 	SUBSTRING(@whichPlaceHolder,3,8)
 
	SET @modifiedQuestion = REPLACE(@pQuestionTemplate, @whichPlaceHolder, isnull((SELECT @whichField FROM Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
 
	SET @modifiedQuestion = 'SELECT REPLACE('''+@pQuestionTemplate+''', '''+@whichPlaceHolder+''', isnull((SELECT '+@whichField+' FROM Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = '+convert(varchar,@pAppCatID)+'),''[[Data missing]]''))'
 
	exec (@modifiedQuestion)
END

Open in new window

Mark Wills

Oh, and to use it :

exec SP_Substitute_Data_Variable 1 ,'what is the price of fish $$Q02_Data'

wsturdev

ASKER

Well, you both confirmed what I was beginning to understand. That is, you cannot do dynamic SQL inside a UDF.
mark_wills -- Your SPROC is almost literally identical to one I created myself, but I cannot use it because I am trying to use the routine within a View.
angelIII -- you gave me the clue to develop the solution. I have attached the code I developed as a result of your comment.
Processing time for 7,700 records went from 2:26 to 0:06

CREATE FUNCTION [dbo].[Substitute_Data_Variable]
	(
	@pAppCatID INT,
	@pQuestionTemplate VARCHAR(400)
	)
	RETURNS VARCHAR(4000)
AS
 
BEGIN
	DECLARE @pQuestion     VARCHAR(4000)
	DECLARE @whichPlaceHolder varchar(4000)
	DECLARE @whichField varchar(4000)
		
	SET @whichPlaceHolder = SUBSTRING(@pQuestionTemplate,CHARINDEX ( '$$' ,@pQuestionTemplate , 1 ),12)
	SET @whichField = 	SUBSTRING(@whichPlaceHolder,3,8)
 
	SELECT @pQuestion = 
		CASE @WhichPlaceHolder
			WHEN '$$Q01_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q01_Data$$', isnull((SELECT Q01_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q02_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q02_Data$$', isnull((SELECT Q02_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q03_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q03_Data$$', isnull((SELECT Q03_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q04_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q04_Data$$', isnull((SELECT Q04_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q05_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q05_Data$$', isnull((SELECT Q05_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q06_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q06_Data$$', isnull((SELECT Q06_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q07_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q07_Data$$', isnull((SELECT Q07_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q08_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q08_Data$$', isnull((SELECT Q08_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q09_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q09_Data$$', isnull((SELECT Q09_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q10_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q10_Data$$', isnull((SELECT Q10_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q11_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q11_Data$$', isnull((SELECT Q11_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q12_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q12_Data$$', isnull((SELECT Q12_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q13_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q13_Data$$', isnull((SELECT Q13_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q14_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q14_Data$$', isnull((SELECT Q14_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q15_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q15_Data$$', isnull((SELECT Q15_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q16_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q16_Data$$', isnull((SELECT Q16_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q17_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q17_Data$$', isnull((SELECT Q17_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q18_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q18_Data$$', isnull((SELECT Q18_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q19_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q19_Data$$', isnull((SELECT Q19_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q20_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q20_Data$$', isnull((SELECT Q20_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q21_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q21_Data$$', isnull((SELECT Q21_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q22_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q22_Data$$', isnull((SELECT Q22_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q23_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q23_Data$$', isnull((SELECT Q23_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q24_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q24_Data$$', isnull((SELECT Q24_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q25_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q25_Data$$', isnull((SELECT Q25_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q26_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q26_Data$$', isnull((SELECT Q26_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q27_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q27_Data$$', isnull((SELECT Q27_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q28_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q28_Data$$', isnull((SELECT Q28_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q29_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q29_Data$$', isnull((SELECT Q29_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q30_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q30_Data$$', isnull((SELECT Q30_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q31_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q31_Data$$', isnull((SELECT Q31_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q32_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q32_Data$$', isnull((SELECT Q32_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q33_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q33_Data$$', isnull((SELECT Q33_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q34_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q34_Data$$', isnull((SELECT Q34_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q35_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q35_Data$$', isnull((SELECT Q35_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q36_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q36_Data$$', isnull((SELECT Q36_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q37_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q37_Data$$', isnull((SELECT Q37_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q38_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q38_Data$$', isnull((SELECT Q38_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q39_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q39_Data$$', isnull((SELECT Q39_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q40_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q40_Data$$', isnull((SELECT Q40_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q41_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q41_Data$$', isnull((SELECT Q41_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q42_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q42_Data$$', isnull((SELECT Q42_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q43_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q43_Data$$', isnull((SELECT Q43_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q44_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q44_Data$$', isnull((SELECT Q44_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q45_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q45_Data$$', isnull((SELECT Q45_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q46_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q46_Data$$', isnull((SELECT Q46_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q47_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q47_Data$$', isnull((SELECT Q47_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q48_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q48_Data$$', isnull((SELECT Q48_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q49_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q49_Data$$', isnull((SELECT Q49_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q50_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q50_Data$$', isnull((SELECT Q50_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q51_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q51_Data$$', isnull((SELECT Q51_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q52_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q52_Data$$', isnull((SELECT Q52_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q53_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q53_Data$$', isnull((SELECT Q53_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q54_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q54_Data$$', isnull((SELECT Q54_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q55_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q55_Data$$', isnull((SELECT Q55_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q56_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q56_Data$$', isnull((SELECT Q56_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q57_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q57_Data$$', isnull((SELECT Q57_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q58_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q58_Data$$', isnull((SELECT Q58_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q59_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q59_Data$$', isnull((SELECT Q59_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
			WHEN '$$Q60_Data' THEN
				REPLACE(@pQuestionTemplate, '$$Q60_Data$$', isnull((SELECT Q60_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.Apps_Cat_ID = @pAppCatID),'[[Data missing]]'))
		END 
	RETURN @pQuestion
END

Open in new window

wsturdev

ASKER

I split the points because you both provided the initial answer (you cannot do that in a UDF), but angelIII provided the one that gave me the solution.