Return result of dynamic query from function

  • chinmayee

    Old Hand

    Points: 314

    Hi,

    In my application, i'm storing sql query as value of table field in database.

    Like in my NewHireEmployee table, value of JoinDateQuery field can be (select hiredate from employeeInfo) or simply (select getdate)

    I want to fetch this query, evaluate it and then return the result of query.

    I tried to write a function like this,

    CREATE FUNCTION dbo.ExecuteStringAsQuery

    (@empID as nvarchar(500))

    RETURNS Varchar(8000)

    AS

    BEGIN

    DECLARE @sqlquery AS NVARCHAR(500),

    @RESULT AS NVARCHAR(500)

    /* Build Transact-SQL String with parameter value */

    SET @sqlquery = (select JoinDateQuery from NewHireEmployee where empid= + @empID)

    @RESULT = Execute @sqlquery

    return @RESULT

    END

    But it is giving error like

    'Line 10: Incorrect syntax near '@RESULT'.

    I'm using sql server 2000

    Any ideas?

    Thanks in advanced!

  • Jason Selburg

    SSC-Insane

    Points: 24637

    You need to encapsulate the SQL text in single quotes as below.

    CREATE FUNCTION dbo.ExecuteStringAsQuery

    (@empID as nvarchar(500))

    RETURNS Varchar(8000)

    AS

    BEGIN

    DECLARE @sqlquery AS NVARCHAR(500),

    @RESULT AS NVARCHAR(500)

    /* Build Transact-SQL String with parameter value */

    SET @sqlquery = '(select JoinDateQuery from NewHireEmployee where empid=' + @empID + ');'

    @RESULT = Execute @sqlquery

    return @RESULT

    END

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • chinmayee

    Old Hand

    Points: 314

    Hi,

    still it gives error 🙁

    Incorrect syntax near '@RESULT'.

    I want the result of query stored in database table.. I'm succeeded to fetch the qeury. But it is as string.

    I'm not sure of how to trigger a string to be executed as sql query & return resultset.

  • Jason Selburg

    SSC-Insane

    Points: 24637

    Sorry, you got me on this one. :unsure:

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Grant Fritchey

    SSC Guru

    Points: 396384

    You're trying to combine the function of EXEC where it executes an ad hoc sql string with the function of exec where it captures the return status of the execution of a query. They don't go together. Further, you're trying to capture the output of the procedure into a string. That won't work either. Instead, you need to make this a table valued function and simply execute the ad hoc sql string.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Look at sp_executesql

    DECLARE @ReturnValue DATETIME, @EmployeeID INT

    SET @EmployeeID = 1

    exec sp_executesql N'SELECT @JoinDate = JoinDateQuery NewHireEmployee where empid = @EmpID ',

    N'@EmpID int, @JoinDate DATETIME OUTPUT', @EmpID = @EmployeeID, @JoinDate = @ReturnValue OUTPUT

    SELECT @ReturnValue

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Matt Miller (4)

    SSC Guru

    Points: 124208

    Generically - you cannot use EXECUTE or sp_executeSQL within a function. You can only call functions or extended stored procs from within a function, and those ain't it.

    Good news is - you don't need either for what you're doing. You don't need dynamic SQL at all for that matter.

    Try this instead:

    CREATE FUNCTION dbo.GetJoinDate(@empID as nvarchar(500))

    RETURNS Varchar(8000)

    AS

    BEGIN

    DECLARE @RESULT AS NVARCHAR(500)

    Select @result=JoinDateQuery from NewHireEmployee where empid= @empID

    return @RESULT

    END

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • rose_red1947

    Valued Member

    Points: 69

    Try with SET clause before @RESULT = Execute @sqlquery

  • jon-474332

    Right there with Babe

    Points: 729

    Hi there,

    i'm not sure what you really want, but my random guess is that you are trying to execute dynamic sql, and you wanted the results to be returned as a database table.

    Obviously the function you have created will only return you a string as you have specified "RETURNS Varchar(8000)". If you want the function to return a database table then you'll have to use table value function as suggested by Grant Fritchey. But you'll need to define the table you want to return.

    why not just use a stored procedure?

    It can execute dynamic sql + returns results as a result set

    CREATE PROCEDURE [dbo].[spName]

    (@empID AS NVARCHAR(500))

    AS

    BEGIN

    SET NOCOUNT ON;

    SET @SQL = 'select JoinDateQuery from NewHireEmployee where empid=' + @empID

    EXEC(@SQL)

    END

    RETURN

    OR

    Where @SQL = 'select JoinDateQuery from NewHireEmployee where empid=001'

    or

    @SQL = 'select getdate()'

    CREATE PROCEDURE [dbo].[spName]

    (@SQL AS VARCHAR(8000))

    AS

    BEGIN

    SET NOCOUNT ON;

    EXEC(@SQL)

    END

    RETURN

    But be caution about using dynamic sql 😉

    cheers 🙂

  • Ramesh Saive

    SSC-Insane

    Points: 24275

    Assuming that, the column JoinDateQuery will only have two values i.e. "(select hiredate from employeeInfo)" or "(select getdate)" then you can define a bit column HasHireDate instead of JoinDateQuery, with this you can define a simple static query like this....

    SELECT H.EmpID, COALESCE( E.HireDate, GETDATE() ) FROM NewHireEmployee H LEFT JOIN EmployeeInfo E ON H.EmpID = E.EmpID AND H.HasHireDate = 1

    WHERE H.EmpID = @iEmpID

    --Ramesh


  • Matt Miller (4)

    SSC Guru

    Points: 124208

    rose_red1947 (10/24/2007)


    Try with SET clause before @RESULT = Execute @sqlquery

    No....

    Let's try this again. from BOL:

    The types of statements that are valid in a function include:

    DECLARE statements can be used to define data variables and cursors that are local to the function.

    Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.

    Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.

    Control-of-flow statements except TRY...CATCH statements.

    SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

    UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.

    EXECUTE statements calling an extended stored procedure.

    Notice the last line - you can only call Extended stored procs using exec. Dynamic SQL is not allowed.

    And lest you ask: sp_executeSQL is not an extended stored proc, so it's not legal either.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • vinod.andani-874416

    Mr or Mrs. 500

    Points: 527

    Its because, semicolon missing at the end of return statement.

    Use the simplified one below :

    CREATE FUNCTION dbo.ExecuteStringAsQuery (@empID as nvarchar(500))

    RETURNS Varchar(8000)

    AS

    BEGIN

    /* Build Transact-SQL String with parameter value */

    return(select JoinDateQuery from NewHireEmployee where empid= + @empID);

    END

  • Jeff Moden

    SSC Guru

    Points: 996046

    vinod.andani-874416 (10/1/2015)


    Its because, semicolon missing at the end of return statement.

    Use the simplified one below :

    CREATE FUNCTION dbo.ExecuteStringAsQuery (@empID as nvarchar(500))

    RETURNS Varchar(8000)

    AS

    BEGIN

    /* Build Transact-SQL String with parameter value */

    return(select JoinDateQuery from NewHireEmployee where empid= + @empID);

    END

    Not likely. There is currently no requirement in SQL to require a RETURN to be followed by a semi-colon unless the next statement is something like a CTE which wouldn't make sense. The real problem was that folks were trying to execute dynamic SQL from within a function.

    Only because it matters to some people, I'll also point out that this post is 8 years old.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jayasri

    Newbie

    Points: 1

    SET @RESULT = Exec @SQL

    return @RESULT; inccorrecdt syntax near exec .Please help

     

  • Grant Fritchey

    SSC Guru

    Points: 396384

    You can't do that.

    If you want to, you can:

    SELECT @Result = a.SomeColumn
    FROM dbo.ATable AS a
    WHEREa.id=42;

     

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply