SQL srevr 2008函数问题 [英] SQL srevr 2008 Functions Problem

问题描述

我正在尝试使用参数CutomerID和TableCoulumn执行Sql函数.
我想从一个表中返回每个传递的列的值，我不想为要在我的SP中调用的每个列值创建多个函数，这就是为什么选择这种方法的原因.我创建了一个动态查询，并在其中传递参数并将输出分配给我的返回变量，但不幸的是反复得到以下错误:

I''m trying to execuate a Sql Function with parameters CutomerID and TableCoulumn.
I want to return value of each passed column from one table.I don''t want to create multiple functions for each column value to call in my SP.That''s why opted this approach. And I created a Dynamic Query and passing my paramaters inside this and assigning to output to my return variable, but unfortunately repeatedly getting the following error:

Only functions and some extended stored procedures can be executed from within a function

这是我的简单脚本

This is my simple script

DECLARE @DynVal CHAR(20)
 DECLARE @Colname char(50)
 SET @Colname='C.TreatmentProvider'

 DECLARE @sql       NVARCHAR(MAX)
 SET @sql = 'DECLARE  @temp Table(TP char(50)) INSERT Into @temp  '
   SET @sql = @sql +'
 SELECT '+CAST(@Colname AS VARCHAR(50))+ '
FROM tblCase As A INNER  JOIN
tblTreatmentProviderMonthlyReceivedReport As B ON A.CaseID=B.CaseID  INNER JOIN
tblLkpTreatmentProvider As C ON B.TreatmentProviderID=C.TreatmentProviderID INNER JOIN
tblTreatmentProvdrAssignedToCase As D ON D.TreatmentProviderID=B.TreatmentProviderID AND  D.CaseId=B.CaseID
WHERE A.CaseID=20110001 and A.CaseTypeID=6 Select  '+ CAST(@DynVal AS CHAR(20)) +' = TP from  @temp Select * from @temp'

print @sql

EXECUTE sp_executesql
            @sql

我请您帮忙！

I appriciate your help!!

推荐答案

此代码是否在您的函数内.如果是这样，那么它实际上是不允许的.您不能在函数内部执行动态SQL.也许您需要修改您的方法.

请参阅本文. [

Is this code inside your function. If it is, then its really not allowed. You cant execute dynamic SQL inside a function. Perhaps you need to modify your approach.

Refer to this article.[^]

这里没有发生任何事情，您应该使用函数来执行此操作.只需使用一个存储过程，您就可以返回任何您喜欢的东西.

There is nothing going on here that you should be using a function to do this. Just use a stored procedure and you can return anything you like.

我已经找到了解决我自己问题的方法. SQL函数中的查询问题.与此相关的另一个问题是SQL注入安全性.但是，如果您对应用程序中的高安全性不那么在意，那么它会有所帮助.

I have found myself solution to my Question.Ofcourse it''s not accurate one but somewhat helps in resolving this kind of Dynamic Query issues in SQL Functions.One more issue with this is SQL Injection security .However,If you are not much bother about high security in your application it helps.

ALTER  FUNCTION [dbo].[func_GeTPMonyhlyReport](@pCaseID INT,@Colname char(50))
RETURNS CHAR(50)
AS
BEGIN
DECLARE @DynVal Char(50)
    BEGIN

select @DynVal =
case
when @Colname = ''TreatmentProvider''  then  C.TreatmentProvider
when @Colname = ''MonthlyRptReceived'' then  ISNULL(Convert(Varchar,B.MonthlyRptReceived,101),'''')

else ''''
end
FROM tblCase As A INNER  JOIN
tblTreatmentProvdrAssignedToCase As D .........
WHERE A.CaseID=@pCaseID order by B.CreateDt Desc
END


RETURN @DynVal
END

这篇关于SQL srevr 2008函数问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！