Post reply

Saving stored procedure result to a variable

  • February 18, 2004 at 5:02 am

    #107061

    Hi!

    Does anyone know how to store the result of a stored procedure to a variable? I've seen some ways using temporary tables but they are not allowed in a UDF.

    Appreciate whatever inputs you have.

    thanks.

  • February 18, 2004 at 6:47 am

    #494504

    I am sure there is a solution to what you want. Can you provide a bit more specific information? Are you trying to call an SP that retruns a result set from within a UDF?



    Once you understand the BITs, all the pieces come together

  • February 18, 2004 at 7:00 am

    #494507

    I sure hope I can have that solution.

    Anyway, Specifically I would like to store the value of permission_path from the result of the extended procedure below.

    declare @test-2 varchar(50)

    set @test-2 = system_user

    exec master..xp_logininfo @test-2

    I have seen some solution from the web using temporary tables but the thing is temporary tables are not accessible inside a user defined function.

    Appreciate your interest in my problem. thanks in advance.

     

  • February 18, 2004 at 7:17 am

    #494509

    If you are not calling the UDF in a loop (for perfomance reasons), something like ...

    SELECT * FROM OpenRowset('SQLOLEDB', 'Server=(local);Trusted_Connection=yes',

        'SET fmtonly OFF

        exec master..xp_logininfo ') A

    WHERE [Account Name] = 'BUILTIN\Administrators'

    inside your UDF.

    Change SELECT * to SELECT PATH

    Change WHERE [Account Name] = 'BUILTIN\Administrators' to WHERE [Account Name] = @YourVar

     



    Once you understand the BITs, all the pieces come together

Viewing 4 posts - 1 through 3 (of 3 total)

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