SQL srevr 2008 Functions Problem

Question

4.50/5 (2 votes)

See more:

SQL-Server-2008

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

C++

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

Posted 17-Mar-11 11:42am

srinivaskalagara

Add a Solution

Comments

sairam.bhat 18-Mar-11 5:16am

yes good question mine is +5

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

walterhevedeich · Answer 1 · 2011-03-17T11:56:00

Solution 1

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.[^]

Posted 17-Mar-11 11:56am

walterhevedeich

Comments

srinivaskalagara 17-Mar-11 20:20pm

Good article!! But Is it always good to go with Cursors. Looks like OpenQuery option is the better one. I think OpenQuery helps in callling SP inside function. Isn't it?

walterhevedeich 17-Mar-11 20:48pm

That might work. However, it seems to me that you are using variables in your dynamic sql. OpenQuery doesnt allow variables. You might want to try Marcus' suggestion on using a stored procedure instead of a function.

sairam.bhat 18-Mar-11 5:15am

yes sir mine is +5

fjdiewornncalwe · Answer 2 · 2011-03-17T12:42:00

Solution 2

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.

Posted 17-Mar-11 12:42pm

fjdiewornncalwe

Comments

srinivaskalagara 17-Mar-11 20:17pm

How can we use stored procedure inside a function?

fjdiewornncalwe 17-Mar-11 21:12pm

You don't use a stored procedure in a function. A stored procedure should be used instead of a function in most cases.

srinivaskalagara 18-Mar-11 10:50am

I think my problem still not got resolved. As you said if we use Stored Procedure , how can we call this SP from Select statement scripted inside other stored Procedure and use return values.

My question is simple:
Select empid,empname,dbo.func_GetDepartment(@empid,@Depid) from emp;

We can get output directly with above script.

Select empid,empname,dbo.func_GetDepartment(@empid,'Depid') from emp;

if we pass column name 'DepId' the earlier said problem popping up.
How can we use Stored Procedure in this case to include anonymous values from other table without disturbing in the Master script.

sairam.bhat 18-Mar-11 5:15am

yes sir mine is +5

srinivaskalagara · Answer 3 · 2011-03-18T09:27:00

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 Functions Problem

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

SQL srevr 2008 Functions Problem

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us