SQLServerCentral.com / SQL Server 2005 / T-SQL (SS2K5) / Call Stored Procedure from a Function / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 17:23:15 GMT20RE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxYes, that functionality would have helped me now.. :)Wed, 25 Mar 2015 08:01:02 GMTNuhamoviciRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxabout 15 years ago I had the privilege of doing some projects using Interbase and later Firebird, and there you could just select from a stored procedure, or join with a stored procedure without the need to wrap it in a functionSQLServer has evolved enormously since 6.5, and though I must say functions do cover most of the needs I still miss that functionality from time to time :-) I wonder why MS never implemented thisDirkWed, 25 Mar 2015 03:53:41 GMTDirk NaudtsRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]Jeff Moden (3/24/2015)[/b][hr][quote][b]Nuhamovici (3/24/2015)[/b][hr]Yes, it looks like this is so evil, it shouldn't even be attempted![/quote]Uh huh. And AC electricity wouldn't be the norm if people listened to the naysayers that said it shouldn't even be attempted. ;-)[/quote]Hey! Tesla was right - long live Tesla! Look at what he'd done for cars.Tue, 24 Mar 2015 18:04:49 GMTdwain.cRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]Nuhamovici (3/24/2015)[/b][hr]Yes, it looks like this is so evil, it shouldn't even be attempted![/quote]Uh huh. And AC electricity wouldn't be the norm if people listened to the naysayers that said it shouldn't even be attempted. ;-)Tue, 24 Mar 2015 16:39:24 GMTJeff ModenRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxYes, it looks like this is so evil, it shouldn't even be attempted!Tue, 24 Mar 2015 16:18:40 GMTNuhamoviciRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxHeh... I guess that if enough people say it should "never" be done and that it's a "bad practice" without a detailed explanation as to why, we can actually write the 5 monkeys experiment at the human level. ;-)Mon, 23 Mar 2015 11:38:07 GMTJeff ModenRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxThanks for the reply.I agree this is something we should "never" do. It's considered a 'bad practice'.Also, it's not the sp_who stored proc I was interested. It could be any proc for that matter. I wantedsee the "technique" of trying to do this.Why would anyone want to do this? Good question.Imagine you had an "old legacy" ODBC type of application, that let's user type in queries..with the limitationthat they follow the "select field_name from table where condition", It won't allow you to type a "sp_who"or anything like that. However, it does let you run a function within the select. I need to run some stored proceduresbut the tool won't let me. I was thinking of "cheating" and trying to wrap the stored proc in a function. Then I wouldinvoke it select my_function(stored_proc) from table_with_1_row.Thanks for any suggestions.Mon, 23 Mar 2015 10:27:21 GMTNuhamoviciRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]Jeff Moden (3/19/2015)[/b][hr][quote][b]Sean Lange (3/19/2015)[/b][hr][quote][b]Jeff Moden (3/19/2015)[/b][hr][quote][b]Sean Lange (3/19/2015)[/b][hr][quote][b]Nuhamovici (3/19/2015)[/b][hr]Can you share the code that does this? Calling sp_who from within a function?[/quote]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.[/quote]It makes it easy to filter when you're in a hurry... real easy.[/quote]I hope I am just not hearing the sarcasm sound in your response thanks to the internet...if not...well...[/quote]Gosh no. Apologies for the way that came across. No sarcasm or irony there. I have actually used the technique in a pinch. I don't use it for sp_who specifically, though, for the very reason that Solomon posted.[/quote]No need to apologize at all. ;-) I still can't really grasp how this would be useful. I guess I need to read up a bit more here but a proc being called from a udf has a particularly bad code smell to my nose.Fri, 20 Mar 2015 06:58:16 GMTSean LangeRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]Sean Lange (3/19/2015)[/b][hr][quote][b]Jeff Moden (3/19/2015)[/b][hr][quote][b]Sean Lange (3/19/2015)[/b][hr][quote][b]Nuhamovici (3/19/2015)[/b][hr]Can you share the code that does this? Calling sp_who from within a function?[/quote]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.[/quote]It makes it easy to filter when you're in a hurry... real easy.[/quote]I hope I am just not hearing the sarcasm sound in your response thanks to the internet...if not...well...[/quote]Gosh no. Apologies for the way that came across. No sarcasm or irony there. I have actually used the technique in a pinch. I don't use it for sp_who specifically, though, for the very reason that Solomon posted.Thu, 19 Mar 2015 17:09:46 GMTJeff ModenRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]Nuhamovici (3/19/2015)[/b][hr]Can you share the code that does this? Calling sp_who from within a function?[/quote]I cannot think of a reason why anyone would even be using [b]sp_who[/b], let alone want to go through the trouble of setting this up when you can get that information (and more) more easily by simply selecting from one or more of the following DMVs:[ul][li]sys.dm_exec_sessions[/li][li]sys.dm_exec_requests[/li][li]sys.dm_exec_connections[/li][/ul]Take care,Solomon..Thu, 19 Mar 2015 10:39:02 GMTSolomon RutzkyRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]Jeff Moden (3/19/2015)[/b][hr][quote][b]Sean Lange (3/19/2015)[/b][hr][quote][b]Nuhamovici (3/19/2015)[/b][hr]Can you share the code that does this? Calling sp_who from within a function?[/quote]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.[/quote]It makes it easy to filter when you're in a hurry... real easy.[/quote]I hope I am just not hearing the sarcasm sound in your response thanks to the internet...if not...well...Thu, 19 Mar 2015 09:39:59 GMTSean LangeRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]Sean Lange (3/19/2015)[/b][hr][quote][b]Nuhamovici (3/19/2015)[/b][hr]Can you share the code that does this? Calling sp_who from within a function?[/quote]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.[/quote]It makes it easy to filter when you're in a hurry... real easy.Thu, 19 Mar 2015 09:36:52 GMTJeff ModenRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]Nuhamovici (3/19/2015)[/b][hr]Can you share the code that does this? Calling sp_who from within a function?[/quote]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.Thu, 19 Mar 2015 09:32:44 GMTSean LangeRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxCan you share the code that does this? Calling sp_who from within a function?Thu, 19 Mar 2015 09:11:23 GMTNuhamoviciRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]SwePeso (12/10/2008)[/b][hr]I have a function where I call "sp_who2" and take database and username as parameters to the function.Works great.[/quote]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:[code="sql"]EXEC master.dbo.sp_helptext N'sp_who2'[/code]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..Thu, 20 Feb 2014 11:37:58 GMTSolomon RutzkyRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]ishaan99 (12/8/2008)[/b][hr]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[/quote]Hi there. This technically [i]can[/i] be done, to varying degrees based on the method you use. However, I will reiterate what others here have cautioned about:[li]If you attempt any side-effecting operations, it could produce unexpected results.[/li][li]If you are doing a read-only operation (e.g. a SELECT) then it [i]should[/i] be ok (see note below)[/li]There are three ways that I know of to do this:[li]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![/li][li]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![b]NOTE:[/b] If changing the state of the database, you need to make sure to mark the .Net method as IsDeterministic=false (which is the default).[/li][li]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 [i]do[/i] 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).[/li]I have detailed a large portion of this info, and even provided an example of both SQLCLR options, in my article (here on SSC): [url=http://www.sqlservercentral.com/articles/Stairway+Series/105841/]Stairway to SQLCLR Level 2: Sample Stored Procedure and Function[/url]Take care,Solomon...Thu, 20 Feb 2014 11:28:15 GMTSolomon RutzkyRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]Yasemin Örnek (9/19/2012)[/b][hr]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 temptableOr how can I achive this task with sql?Thanks.[/quote]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:[code="sql"]DECLARE @sqltxt nvarchar(max) ;SET @sqltxt='INSERT INTO temptable (col1,col2) select col112,col221 from sometable';exec sp_executesql @sqltxt select * from temptable[/code]That will work with permanent or temporary tables, but not table variables (they will not be in the context of the executed dynamic SQL).Wed, 19 Sep 2012 01:36:27 GMTdwain.cRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxHi , 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 temptableOr how can I achive this task with sql?Thanks.Wed, 19 Sep 2012 01:20:13 GMTyyoRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxI'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?Tue, 22 Feb 2011 00:46:40 GMTsimon155xRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxAlmost 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!Tue, 22 Feb 2011 00:43:38 GMTsimon155xRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]ishaan99 (12/8/2008)[/b][hr]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[/quote]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:[url=http://www.maurodalfreddo.it/archives/97/eseguire-stored-procedures-in-una-funzione-udf-sql-server]http://www.maurodalfreddo.it/archives/97/eseguire-stored-procedures-in-una-funzione-udf-sql-server[/url]MauroThu, 29 Apr 2010 02:41:27 GMTMauro DalfreddoRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxI know... untill you start using sps that do DML, then you canget seriously screwed if you're not carefull... unsupported, I find another way!Wed, 10 Dec 2008 00:33:30 GMTNinja's_RGR'usRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxI have a function where I call "sp_who2" and take database and username as parameters to the function.Works great.Wed, 10 Dec 2008 00:21:47 GMTSwePesoRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx... and you can open fire on the server run with a shotgun and call your service rep for a free replace of the servers too. I wouldn't try that either!MS designed it that way for a very specific reason, I can't recomend it enough to not do that!Tue, 09 Dec 2008 06:01:14 GMTNinja's_RGR'usRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxIt CAN be done if you use OPENROWSET with a loopback linked server.If you should is another question.Tue, 09 Dec 2008 05:34:32 GMTSwePesoRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxExactly... that's because the servers needs to assume that the state of the DB and data will not change by calling the function (again, by design because the server would basically need to recompile the queries after each call to the function... which happens on every rows in the select). If you need to do such modifications, it needs to be done inside a proc.Tue, 09 Dec 2008 04:19:22 GMTNinja's_RGR'usRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]laubenth (12/8/2008)[/b][hr][quote][b]ishaan99 (12/8/2008)[/b][hr]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[/quote]Not sure how you feel about calling functions from functions but you could create thisFunc_XXX(.....) Insert current proc code here making the proc a proper functionProc_XXX(.....) Select * From Func_XXX(....)Use Func_XXX from your other function.[/quote]If the code contains any insert/update/delete statements to permanent tables (e.g. not table variables and not temporary tables), it isn’t possible. Inside a function you can not modify data and can not execute stored procedure. AdiMon, 08 Dec 2008 22:35:40 GMTAdi Cohn-120898RE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]ishaan99 (12/8/2008)[/b][hr]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[/quote]Not sure how you feel about calling functions from functions but you could create thisFunc_XXX(.....) Insert current proc code here making the proc a proper functionProc_XXX(.....) Select * From Func_XXX(....)Use Func_XXX from your other function.Mon, 08 Dec 2008 22:21:53 GMTlaubenthRE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspx[quote][b]Ninja's_RGR'us (12/8/2008)[/b][hr]Can't be done... and shouldn't be done (by design).What exactly do you want to do?[/quote]Can't be done in [u]T-SQL[/u] functions. You might be able to get away with it through CLR (depending on what is being done). That being said - totally agree about the SHOULDN'T part...Mon, 08 Dec 2008 10:21:56 GMTMatt Miller (#4)RE: Call Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxCan't be done... and shouldn't be done (by design).What exactly do you want to do?Mon, 08 Dec 2008 10:05:07 GMTNinja's_RGR'usCall Stored Procedure from a Functionhttp://www.sqlservercentral.com/Forums/Topic615647-338-1.aspxHas 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. TIAMon, 08 Dec 2008 09:49:19 GMTishaan99