none
Can I call a Sql scalar-valued function within a store procedure

    Question

  • I am using Sql 2000. I have the actual Sql function name stored in a table. In a "Select" statement on that table, can I call that function and passing on the parameter and return with a value from that function to a field on a dataset?

    Paul

     

    Kahlua

    Tuesday, July 23, 2013 9:14 PM

Answers

  • The simple answer is no, there is no way to use a variable or column value in place of a function or table name. On the on the other hand you could read in that name, construct a statement with the column value and dynamically execute the statement you generated.

    Tom G.

    • Proposed as answer by Kalman TothModerator Wednesday, July 24, 2013 1:18 AM
    • Marked as answer by pmak Wednesday, July 24, 2013 8:22 PM
    Wednesday, July 24, 2013 1:05 AM

All replies

  • The simple answer is no, there is no way to use a variable or column value in place of a function or table name. On the on the other hand you could read in that name, construct a statement with the column value and dynamically execute the statement you generated.

    Tom G.

    • Proposed as answer by Kalman TothModerator Wednesday, July 24, 2013 1:18 AM
    • Marked as answer by pmak Wednesday, July 24, 2013 8:22 PM
    Wednesday, July 24, 2013 1:05 AM
  • As Tom indicated you need to use dynamic SQL:

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Wednesday, July 24, 2013 1:19 AM
  • Short answer: No.

    Better answer: we do not use UDFs in stored procedures. Also,  columns are not fields. 

    Post your code and maybe we can help you. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, July 24, 2013 2:45 AM
  • Here i have a simple example of using a user define function within a stored procedure.

    CREATE FUNCTION GETTIME(@HR INT)
    RETURNS NVARCHAR(10)
    AS 
    BEGIN
    	RETURN (CONVERT(CHAR(10),DATEADD(HH,@HR,GETDATE()),108))    -- adding input hour to the current date.
    END
    ---------------------------------------------------
    CREATE PROCEDURE SP_TESTING
    AS
    BEGIN
    	DECLARE @TIME NVARCHAR(10)
    	SET @TIME=dbo.GETTIME(2)      -- GETTIME IS A USER DEFINE FUNCTION RETURNING A SCALAR VALUE
        SELECT @TIME AS CURRENTTIME
    END
    ---------------------------------------------------
    EXEC SP_TESTING


    Regards, RSingh

    Wednesday, July 24, 2013 3:53 AM