December 8, 2008 at 9:49 am
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
December 8, 2008 at 10:05 am
Can't be done... and shouldn't be done (by design).
What exactly do you want to do?
December 8, 2008 at 10:21 am
Ninja's_RGR'us (12/8/2008)
Can't be done... and shouldn't be done (by design).What exactly do you want to do?
Can't be done in T-SQL 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...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 8, 2008 at 10:21 pm
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
Not sure how you feel about calling functions from functions but you could create this
Func_XXX(.....) Insert current proc code here making the proc a proper function
Proc_XXX(.....) Select * From Func_XXX(....)
Use Func_XXX from your other function.
December 8, 2008 at 10:35 pm
laubenth (12/8/2008)
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
Not sure how you feel about calling functions from functions but you could create this
Func_XXX(.....) Insert current proc code here making the proc a proper function
Proc_XXX(.....) Select * From Func_XXX(....)
Use Func_XXX from your other function.
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.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 9, 2008 at 4:19 am
Exactly... 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.
December 9, 2008 at 5:34 am
It CAN be done if you use OPENROWSET with a loopback linked server.
If you should is another question.
N 56°04'39.16"
E 12°55'05.25"
December 9, 2008 at 6:01 am
... 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!
December 10, 2008 at 12:21 am
I have a function where I call "sp_who2" and take database and username as parameters to the function.
Works great.
N 56°04'39.16"
E 12°55'05.25"
December 10, 2008 at 12:33 am
I know... untill you start using sps that do DML, then you canget seriously screwed if you're not carefull... unsupported, I find another way!
April 29, 2010 at 2:41 am
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
February 22, 2011 at 12:43 am
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!
February 22, 2011 at 12:46 am
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?
September 19, 2012 at 1:20 am
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.
September 19, 2012 at 1:36 am
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply