SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Call Stored Procedure from a Function


Call Stored Procedure from a Function

Author
Message
Mauro Dalfreddo
Mauro Dalfreddo
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 27
ishaan99 (12/8/2008)
Has anyone tried calling a stored procdure from a user defined function. I have a procedure as Proc_XXX with 7 parameters if i do exec Proc_XXX(null,null,null,null,null,'1/1/1998','1/1/2007') i am getting the result set. Is it possible to have the same results when calling thru a function .

call Proc_XXX(null,null,null,null,null,'1/1/1998','1/1/2007')

any help on this will be greatly appreciated. TIA


Hi, you should use a stored procedure, becouse by design it is impossible modify the database context outside the function.
Recently I found a workaround using external .NET function, I described well in my site.
The post is in Italian, but you can find useful informations:

http://www.maurodalfreddo.it/archives/97/eseguire-stored-procedures-in-una-funzione-udf-sql-server

Mauro
simon155x
simon155x
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 2
Almost loathe to answer it, but I never know what the needs are.

It is possible. Perhaps you're trying to avoid the poorly performing table variables in 2005+ and use a reporting tool that won't submit proc calls or something. Who knows.

You shouldn't call procedures through functions, and this approach REALLY isn't advised, but one method of doing it (there are several) using only sql, in an imaginary scenario, without using any table variables is:

1) Create your stored procedure, accepting whatever paramters are needed. In the stored procedure, implement a "tidyup" routine for previous result sets. Output the data to a perm table. The stored procedure then acts as a specific result set refresh mechanism. A status table could store the last refresh dates, status etc.
2) Create a table function. Use command shell (ewww) to call the stored procedure through osql, returning the current status of that result set.
3) Run a report against the perm table, to retrieve data, or include the function to refresh it.

Did it once on my dev box, just for a laugh and it does work. Don't think any of it is best practise, but on principle I had to prove it could be done Wink

And yes, depending on the complexity and size of the result set, you will most likely notice a significant improvement in speed, over table variables for derived data, plus the result set is reusable if stored, thus potentially cutting overheads.

If you focus on those points and ignore the huge gaping holes *cough security cough* and complete lack of best practise, it's great!
simon155x
simon155x
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 2
I'm curious - I assume the reason you haven't done a flat conversion of the stored procedure to a function is because of data volumes and and the horrific performance issues with table variables through 2005 or something?
yyo
yyo
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 15
Hi , I want to ask a question ,
Is it possible executing dynamic sql and store values in a temp table with WITH clause like below code?

DECLARE @sqltxt nvarchar(max) ;

SET @sqltxt='select col112,col221 from sometable';

WITH temptable (col1,col2) as
(exec sp_executesql @sqltxt )
select * from temptable

Or how can I achive this task with sql?
Thanks.
dwain.c
dwain.c
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28231 Visits: 6431
Yasemin Örnek (9/19/2012)
Hi , I want to ask a question ,
Is it possible executing dynamic sql and store values in a temp table with WITH clause like below code?

DECLARE @sqltxt nvarchar(max) ;

SET @sqltxt='select col112,col221 from sometable';

WITH temptable (col1,col2) as
(exec sp_executesql @sqltxt )
select * from temptable

Or how can I achive this task with sql?
Thanks.


First of all, you shouldn't hijack other peoples' threads when you can easily start a new one of your own. Secondly, I shouldn't encourage you in doing so by answering your questions. :-)

But since I answered before realizing this, I'll say I'm pretty sure that your syntax will not work. Try it!

You could try do something like this:


DECLARE @sqltxt nvarchar(max) ;

SET @sqltxt='INSERT INTO temptable (col1,col2) select col112,col221 from sometable';

exec sp_executesql @sqltxt

select * from temptable




That will work with permanent or temporary tables, but not table variables (they will not be in the context of the executed dynamic SQL).


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Solomon Rutzky
Solomon Rutzky
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4751 Visits: 3057
ishaan99 (12/8/2008)
Has anyone tried calling a stored procedure from a user defined function. I have a procedure as Proc_XXX with 7 parameters if i do exec Proc_XXX(null,null,null,null,null,'1/1/1998','1/1/2007') i am getting the result set. Is it possible to have the same results when calling through a function .

call Proc_XXX(null,null,null,null,null,'1/1/1998','1/1/2007')

any help on this will be greatly appreciated. TIA


Hi there. This technically can be done, to varying degrees based on the method you use. However, I will reiterate what others here have cautioned about:
  • If you attempt any side-effecting operations, it could produce unexpected results.

  • If you are doing a read-only operation (e.g. a SELECT) then it should be ok (see note below)


  • There are three ways that I know of to do this:

  • OPENROWSET / OPENQUERY (mentioned by SwePeso): this will make an external connection that will be a new session (i.e. @@SPID) so no shared share info such as local temp tables (e.g. #tmp) or CONTEXT_INFO. Global temp tables are accessible (e.g. ##tmp). There will also be some additional, and possibly larger-impacting security configuration needed depending on which of those two you are using. This method can allow for changing the state of the database!

  • SQLCLR using standard / regular external connection: this will make an external connection that will be a new session (i.e. @@SPID) so no shared share info such as local temp tables (e.g. #tmp) or CONTEXT_INFO. Global temp tables are accessible (e.g. ##tmp). The only security configuration needed is setting the Assembly to EXTERNAL_ACCESS, but that configuration affects only the Assembly so a fairly narrow security impact. This method can allow for changing the state of the database!

    NOTE: If changing the state of the database, you need to make sure to mark the .Net method as IsDeterministic=false (which is the default).

  • SQLCLR using in-process Context Connection: this is the only option for tapping into the internal connection. This is part of the same session (i.e. same @@SPID) so you do have access to local temp tables (e.g. #tmp) and CONTEXT_INFO! No security changes are needed as this can be done with an Assembly marked as SAFE. This method only allows for read-only interactions and cannot change the state of the database. This method has the same restrictions as T-SQL User-Defined Functions (well, with the obvious exception of not being able to call Stored Procedures).


  • I have detailed a large portion of this info, and even provided an example of both SQLCLR options, in my article (here on SSC): Stairway to SQLCLR Level 2: Sample Stored Procedure and Function

    Take care,
    Solomon...

    SQL# - https://SQLsharp.com/
    Sql Quantum Lift - http://SqlQuantumLift.com/
    Solomon Rutzky
    Solomon Rutzky
    SSCarpal Tunnel
    SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

    Group: General Forum Members
    Points: 4751 Visits: 3057
    SwePeso (12/10/2008)
    I have a function where I call "sp_who2" and take database and username as parameters to the function.
    Works great.


    Hi there. I just wanted to mention that while OPENROWSET does indeed work here, and that it is a read-only operation so it should be stable, there is a quicker and simpler solution. If you do the following:

    EXEC master.dbo.sp_helptext N'sp_who2'



    you will see the full query logic of sp_who2. Be warned: it ain't pretty ;-). But, it does provide for seeing how they went about getting that data. So, you could just write a new Stored Procedure, passing in any filter params that you want, that does more than your setup of passing in database name and username to adjust the query.

    That being said, it would probably be even better to just query the new DMVs directly :-). The point being: many of the Microsoft provided "sp_" procs are in plain text so you can get the definition to learn what they are doing.

    Take care,
    Solomon..

    SQL# - https://SQLsharp.com/
    Sql Quantum Lift - http://SqlQuantumLift.com/
    Nuhamovici
    Nuhamovici
    SSC Rookie
    SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

    Group: General Forum Members
    Points: 44 Visits: 71
    Can you share the code that does this? Calling sp_who from within a function?



    Sean Lange
    Sean Lange
    SSC Guru
    SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

    Group: General Forum Members
    Points: 100330 Visits: 18172
    Nuhamovici (3/19/2015)
    Can you share the code that does this? Calling sp_who from within a function?


    Why? What could you possibly gain from calling a stored proc from within a function? You should instead get rid of the function entirely.

    If you can explain what you are trying to do we can help you find a better approach than calling a stored proc from a function.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
    Understanding and Using APPLY (Part 1)
    Understanding and Using APPLY (Part 2)
    Jeff Moden
    Jeff Moden
    SSC Guru
    SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)

    Group: General Forum Members
    Points: 335843 Visits: 42587
    Sean Lange (3/19/2015)
    Nuhamovici (3/19/2015)
    Can you share the code that does this? Calling sp_who from within a function?


    Why? What could you possibly gain from calling a stored proc from within a function? You should instead get rid of the function entirely.

    If you can explain what you are trying to do we can help you find a better approach than calling a stored proc from a function.


    It makes it easy to filter when you're in a hurry... real easy.

    --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

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search