Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Call Stored Procedure from a Function Expand / Collapse
Author
Message
Posted Thursday, April 29, 2010 2:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 4, 2011 8:33 AM
Points: 8, 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
Post #912617
Posted Tuesday, February 22, 2011 12:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 4, 2011 7:06 AM
Points: 2, 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 ;)

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!
Post #1067437
Posted Tuesday, February 22, 2011 12:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 4, 2011 7:06 AM
Points: 2, 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?
Post #1067439
Posted Wednesday, September 19, 2012 1:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 28, 2012 4:47 AM
Points: 1, Visits: 10
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.
Post #1361164
Posted Wednesday, September 19, 2012 1:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1361169
Posted Thursday, February 20, 2014 11:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:23 AM
Points: 368, Visits: 1,950
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# - http://www.SQLsharp.com/
    Post #1543649
    Posted Thursday, February 20, 2014 11:37 AM


    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Friday, October 17, 2014 12:23 AM
    Points: 368, Visits: 1,950
    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# - http://www.SQLsharp.com/
    Post #1543651
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse