InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 07:50:21 GMT20http://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.chttp://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 GMTyyohttp://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 GMTsimon155xhttp://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 GMTsimon155xhttp://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 Dalfreddohttp://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'ushttp://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 GMTSwePesohttp://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'ushttp://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 GMTSwePesohttp://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'ushttp://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-120898http://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 GMTlaubenthhttp://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)http://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'ushttp://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