Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Unanswered: Using EXECUTE statements calling an extended stored procedures from function..

    Hi, all
    I'm using Sql server 2000
    I want to make select statement dynamically and return table using function.
    in sp, I've done this but, in function I don't know how to do so.
    (I have to create as function since our existing API..)

    Following is my tials...
    1.
    alter Function fnTest
    ( @fromTime datetime, @toTime datetime)
    RETURNS Table
    AS

    RETURN Exec spTest @from, @to
    GO

    Yes, it give syntax error..

    2. So, I found the following


    From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..

    "The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: "
    .....
    * EXECUTE statements calling an extended stored procedures.

    So, I tried.

    alter Function fnTest
    ( @fromTime datetime, @toTime datetime)
    RETURNS Table
    AS

    RETURN Exec master..xp_msver
    GO

    It doesn't work... syntax err...

    Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?


    Now, I'm stuck.. how can I create dynamic select statement using function?

    I want to know if it's possible or not..
    Last edited by goodmorningsky; 04-29-04 at 19:14.
    MCSD .NET, SCJP, SCJWD

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to convert your inline function to multi-statement table-valued function. Their syntax, behavior, and limitations are differrent. Inline functions do NOT allow anything other than SELECT, so your attempt to use either EXECUTE or anything else is doomed to fail.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Quote Originally Posted by rdjabarov
    You need to convert your inline function to multi-statement table-valued function. Their syntax, behavior, and limitations are differrent. Inline functions do NOT allow anything other than SELECT, so your attempt to use either EXECUTE or anything else is doomed to fail.
    No you don't. You can't execute a stored procedure in a function. You will have to figure out a way to do it with stored procedures.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Apr 2004
    Posts
    49
    I don't get it still..

    then what's meaning of the following statement?
    EXECUTE statements calling an extended stored procedures.

    please check following link and Remarks section.
    http://msdn.microsoft.com/library/de...reate_7r1l.asp

    I need example of this case.
    MCSD .NET, SCJP, SCJWD

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Derrick,

    Read BOL on the subject of functions, their differences, and limitations. I've written enough of them by now, so if I said that it's an inline function, it means that it's nothing but that. Table-valued function is what the original poster needs, end of story...Next!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    An extended stored procedure in 2000 is a dll you register as an extended stored procedure in SQL Server. It's not a regular stored procedure. For more information, look up extended stored procedures in Books Online or on msdn.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Quote Originally Posted by rdjabarov
    Derrick,

    Read BOL on the subject of functions, their differences, and limitations. I've written enough of them by now, so if I said that it's an inline function, it means that it's nothing but that. Table-valued function is what the original poster needs, end of story...Next!

    I've read BOL a few times. I've also read several other books on SQL Server and written a lot of functions. There's no reason to get in a pissing contest. I was just pointing out that he won't be able to EXEC his stored procedure in "any" kind of function.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually, it's not a contest, and if it is, - you already lost. The question the poster asked was referring to ability (or inability) to execute an extended stored procedure from a UDF. My answer was YES, providing that it's a table-valued function, not an inline one. For your convenience, here's an excerpt from BOL on the subject at hand:

    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.


    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Yeah, I know how it works. Now, why don't you show us how he can do what he's trying to do inside of function. You can't execute this from a function.

    1. alter Function fnTest
    ( @fromTime datetime, @toTime datetime)
    RETURNS Table
    AS

    RETURN Exec spTest @from, @to
    GO


    or this:

    2.

    alter Function fnTest
    ( @fromTime datetime, @toTime datetime)
    RETURNS Table
    AS

    RETURN Exec master..xp_msver
    GO

    It doesn't matter what kind of function it is, including a table function.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Both of your examples demonstrate an inline function, not a table-valued function.

    The example below demonstrates how you can call an extended stored procedure from within a multi-statement (table-valued in this case) function. A SELECT from this function returns an empty result set, because you cannot populate a table variable (the only type of table allowed in a function) from an EXECUTE statement.

    create function dbo.fn_execute()
    returns @tbl table (txt varchar(8000) null)
    as begin
    exec master.dbo.xp_msver
    return
    end
    go
    select * from dbo.fn_execute()
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Quote Originally Posted by rdjabarov
    Both of your examples demonstrate an inline function, not a table-valued function.

    The example below demonstrates how you can call an extended stored procedure from within a multi-statement (table-valued in this case) function. A SELECT from this function returns an empty result set, because you cannot populate a table variable (the only type of table allowed in a function) from an EXECUTE statement.

    create function dbo.fn_execute()
    returns @tbl table (txt varchar(8000) null)
    as begin
    exec master.dbo.xp_msver
    return
    end
    go
    select * from dbo.fn_execute()
    Those weren't my examples. They were his. That's why I told him he can't do it in a function. If you can show me how to get that to work and actually return the resultset of the xp_msver, I'll just be thrilled though. Until then, he needs to write a stored procedure to do it, unless of course you know another way to do it.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    As I said earlier, -

    "A SELECT from this function returns an empty result set, because you cannot populate a table variable (the only type of table allowed in a function) from an EXECUTE statement."
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To answer the original question, yes, it can be done. No, it is a VERY bad thing to do in every case that I've seen in SQL 2000. There are some good reasons that the Yukon developers didn't address this issue.

    -PatP

  14. #14
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    How? How can he execute a stored procedure inside of a function and return the result set? How can he get either one of his examples to even remotely work?

    He can't. That is the answer to the original question. If he can, please show me how so I can learn.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •