Execute SQL string in a scalar valued function

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)

Execute SQL string in a scalar valued function

Author

Topic

myorick
Starting Member

3 Posts

Posted - 2010-01-07 : 11:30:48

I need to execute a SQL string in a function and I haven't been able to get it to work. Any advice? I have put the code from my function below.

***********************************************

ALTER FUNCTION [dbo].[ufGetPercentage_TwoValues](
@AssessmentKey int,
@QuestionCode varchar(50),
@Value1 int,
@Value2 int
)
RETURNS int
AS
BEGIN

DECLARE @NumberAnswered decimal(6,2),
@TotalAnswered decimal(6,2),
@Query1 nvarchar(2000),
@Query2 nvarchar(2000)

Set @Query1 = N'SELECT @TotalAnswered = COUNT(SSPAnswersKey) FROM dbo.tblAssessment_m2m_SerialNo INNER JOIN dbo.tblAssessment_SSPAnswers ON dbo.tblAssessment_m2m_SerialNo.AssessmentSerialNoKey = dbo.tblAssessment_SSPAnswers.AssessmentSerialNoKey WHERE (dbo.tblAssessment_m2m_SerialNo.AssessmentKey = @AssessmentKey) AND (dbo.tblAssessment_SSPAnswers.' + @QuestionCode + ' > 0)'

Set @Query2 = N'SELECT @NumberAnswered = COUNT(SSPAnswersKey) FROM dbo.tblAssessment_m2m_SerialNo INNER JOIN dbo.tblAssessment_SSPAnswers ON dbo.tblAssessment_m2m_SerialNo.AssessmentSerialNoKey = dbo.tblAssessment_SSPAnswers.AssessmentSerialNoKey WHERE (dbo.tblAssessment_m2m_SerialNo.AssessmentKey = @AssessmentKey ) AND ((dbo.tblAssessment_SSPAnswers.' + @QuestionCode + ' = @Value1) OR (dbo.tblAssessment_SSPAnswers.' + @QuestionCode + ' = @Value2))'

exec @Query1
exec @Query2
RETURN @NumberAnswered / @TotalAnswered * 100
END

***********************************************

Any help will be greatly appreciated. Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 11:42:09

you need to use sp_executesql instead of EXEC if you need to return the variable as OUTPUT value

http://msdn.microsoft.com/en-us/library/ms188001.aspx

myorick
Starting Member

3 Posts

Posted - 2010-01-07 : 12:26:30

That was what I did originally, but I got an error that said only a function or an extended stored procedure could be executed in a function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 12:30:24

aha... you're using dynamic sql inside function? you cant do that. can i ask need of that? what does @QuestionCode contain?

myorick
Starting Member

3 Posts

Posted - 2010-01-07 : 15:02:35

@QuestionCode is the field name. I have to do 100+ of these calculations and was staying as optimistic as possible that I could write this dynamically instead of having 100+ functions. :(

I feared I couldn't do it, but really hoped I just hadn't searched hard enough. Oh well. Thank you for supplying the definitive answer.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-07 : 16:28:49

I would use a table valued function that returns a table that you join to on a key field on the object that is calling this function.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 02:31:42

quote:
Originally posted by myorick

@QuestionCode is the field name. I have to do 100+ of these calculations and was staying as optimistic as possible that I could write this dynamically instead of having 100+ functions. :(

I feared I couldn't do it, but really hoped I just hadn't searched hard enough. Oh well. Thank you for supplying the definitive answer.

do you mean you've separate column for each of question code? then i should say your table is not properly designed. have you heard of normalisation?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources