none
Calling a stored procedure in a function

    Question

  • Hi

    Sorry for creating a duplicate thread though my problem is same but i didnt got the solution.

    I am creating a function which calls a stored procedure in it but when i call that function in a view or select statement i get this error:

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

    Below is my function that i have created,

    ALTER Function GetPropertyParameterRate( @PropertyID int , @ParameterDescription varchar(250))  
    Returns nvarchar(max)  
    As  
    Begin  
    
     
    Declare @Filter varchar(max)
    
    Set @Filter = 'Select * From (
    Select CASE WHEN ParameterDescription LIKE ''%RATE%'' THEN ParameterValue END as ParameterDescription
    From CM_MDT_PROPERTYPARAMETERDETAILS P
    JOIN CM_MST_UOM U ON P.UOMID = U.UOMID
    Where PropertyID = ' + CAST(@PropertyID AS VARCHAR(MAX)) + ' AND ParameterCode IN ('+ dbo.PropertyParameters(@PropertyID,'Basic Sale Price') +')
    ) as Q Where Q.ParameterDescription is not null'
    
    EXEC sp_ExecuteSql @Filter
    
    
    
    Return @Filter
    
    
    End 
    

    The error is because of the line "EXEC sp_ExecuteSql @Filter".

    There is a work around for calling stored procedure in a function in the link below

    http://www.sqlservercentral.com/Forums/Topic270460-338-1.aspx

    but i get that query in return though i want the value.

     

     

    Monday, January 10, 2011 12:35 PM

Answers

  • While you can use OpenQuery as a workaround, you cannot use openQuery for situations in which the SQL text is something other than a string constant -- that is, dynamic SQL.  While the post that you reference from SQL Server Central does point out the work around, if you look at the last response you will see that the gentleman is more-or-less saying the same thing: You cannot use openQuery with a variable SQL string.

    I also suggest running your crystal report with a stored procedure rather than a function.  I see you state you cannot use the stored procedure -- please explain why that is.

    EDIT:

    Since the focus of the dynamic SQL might well be that IN comparator, you might be able to modifiy the function so that you don't have to use dynamic SQL.  If that is the case then you should be able to use the function without converting to a stored procedure.

    Monday, January 10, 2011 2:02 PM
    Moderator
  • > I can not use stored procedure as i have to use that function in crystal reports.

    You cannot call stored procedures from functions in SQL Server. You cannot use dynamic SQL in functions in SQL Server. Those are facts. Trying to work against that, is as constructive as banging your head to a wall.

    On the other hand, you can use stored procedures in Crystal. I know, we have Crystal in the system I work with, and it's stored procedure calls all over the place. Granted, we still use Crystal 8.5, but I don't think have removed such an essential feature.

    On the other hand, looking at your code, I can't see any need for dynamic SQL at all. You could write it as:

    Select * From (
    Select CASE WHEN ParameterDescription LIKE '%RATE%' THEN ParameterValue END as ParameterDescription
    From CM_MDT_PROPERTYPARAMETERDETAILS P
    JOIN CM_MST_UOM U ON P.UOMID = U.UOMID
    Where PropertyID = @PropertyID
      AND ParameterCode IN
          (SELECT list_to_table(dbo.PropertyParameters(@PropertyID,
                                'Basic Sale Price'))
    ) as Q
    Where Q.ParameterDescription is not null

    You can find several list_to_table functions on my web site:
    http://www.sommarskog.se/arrays-in-sql.html

    Yet then again it would be better of that function returned a set of values and not a comma-separated lists.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Monday, January 10, 2011 10:15 PM
  • Well, i got a function to break comma separated string into table and my issue seems to be solved though the thread was created for some other issue.

    The link for reference is below:

    http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx


    This is pretty much what I (Erland too, I think) wanted to stear you toward; good job.
    Wednesday, January 12, 2011 1:58 PM
    Moderator

All replies

  • You cannot execute dynamic SQL in user-defined functions. Related link: http://blog.sqlauthority.com/2007/05/29/sql-server-user-defined-functions-udf-limitations/

    You can architect on the other hand dynamic SQL stored procedure.

    See examples at the following link: http://www.sqlusa.com/bestpractices/dynamicsql/

     


    Kalman Toth, Microsoft Community Contributor 2011; SQL 2008 GRAND SLAM
    • Proposed as answer by Christa Kurschat Monday, January 10, 2011 12:49 PM
    • Unproposed as answer by AGovil Tuesday, January 11, 2011 5:12 AM
    Monday, January 10, 2011 12:41 PM
    Moderator
  • I can not use stored procedure as i have to use that function in crystal reports.
    Monday, January 10, 2011 1:18 PM
  • You can develop stored procedure that returns results and use that in Crystal Reports. While your dynamic sql will be part of that new stored procedure 
    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Monday, January 10, 2011 1:29 PM
  • While you can use OpenQuery as a workaround, you cannot use openQuery for situations in which the SQL text is something other than a string constant -- that is, dynamic SQL.  While the post that you reference from SQL Server Central does point out the work around, if you look at the last response you will see that the gentleman is more-or-less saying the same thing: You cannot use openQuery with a variable SQL string.

    I also suggest running your crystal report with a stored procedure rather than a function.  I see you state you cannot use the stored procedure -- please explain why that is.

    EDIT:

    Since the focus of the dynamic SQL might well be that IN comparator, you might be able to modifiy the function so that you don't have to use dynamic SQL.  If that is the case then you should be able to use the function without converting to a stored procedure.

    Monday, January 10, 2011 2:02 PM
    Moderator
  • > I can not use stored procedure as i have to use that function in crystal reports.

    You cannot call stored procedures from functions in SQL Server. You cannot use dynamic SQL in functions in SQL Server. Those are facts. Trying to work against that, is as constructive as banging your head to a wall.

    On the other hand, you can use stored procedures in Crystal. I know, we have Crystal in the system I work with, and it's stored procedure calls all over the place. Granted, we still use Crystal 8.5, but I don't think have removed such an essential feature.

    On the other hand, looking at your code, I can't see any need for dynamic SQL at all. You could write it as:

    Select * From (
    Select CASE WHEN ParameterDescription LIKE '%RATE%' THEN ParameterValue END as ParameterDescription
    From CM_MDT_PROPERTYPARAMETERDETAILS P
    JOIN CM_MST_UOM U ON P.UOMID = U.UOMID
    Where PropertyID = @PropertyID
      AND ParameterCode IN
          (SELECT list_to_table(dbo.PropertyParameters(@PropertyID,
                                'Basic Sale Price'))
    ) as Q
    Where Q.ParameterDescription is not null

    You can find several list_to_table functions on my web site:
    http://www.sommarskog.se/arrays-in-sql.html

    Yet then again it would be better of that function returned a set of values and not a comma-separated lists.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Monday, January 10, 2011 10:15 PM
  • Thanks for the reply Kent & Erland.

    The function that i am calling is:

     dbo.PropertyParameters(@PropertyID,'Basic Sale Price') 
    

    and it returns a comma separated string and this is what the whole story about.

    And the reason, i said, i cant use stored procedure is because i want to change the parameters of the procedure, as and when the group value is changed.

    So using SP makes me think of how to change the parameters, but thats the last step to perform. So, i prefer to do some workaround in that IN clause parameters.

    Actually, the parameter 'Basic Sale Price' has an expression in other column which gives the formula for 'Basic Sale Price' and the expression is contained in format like "[BOOKINGRATE] * [SUPERAREA]" or may be in any mathematical expression and i convert those expression to 'BOOKINGRATE','SUPERAREA' which are again the comma separated parameters whose values can be find in another table. And i want to find values of parameters that i get in comma separated string only and thats why the need of IN clause.

    But if i can break the result(saving each parameter after a comma) and insert in table then that would be useful. Or is there any other way to do?

    Tuesday, January 11, 2011 6:17 AM
  • Well, i got a function to break comma separated string into table and my issue seems to be solved though the thread was created for some other issue.

    The link for reference is below:

    http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

    Tuesday, January 11, 2011 7:12 AM
  • > Well, i got a function to break comma separated string into table and my issue seems to be solved though the thread was created for some other issue.

    Actually, there was a link in my post as well!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Tuesday, January 11, 2011 10:55 PM
  • Well, i got a function to break comma separated string into table and my issue seems to be solved though the thread was created for some other issue.

    The link for reference is below:

    http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx


    This is pretty much what I (Erland too, I think) wanted to stear you toward; good job.
    Wednesday, January 12, 2011 1:58 PM
    Moderator