Call Stored Procedure from a Function

  • 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

  • Can't be done... and shouldn't be done (by design).

    What exactly do you want to do?

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

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

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

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

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

  • ... 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!

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

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

  • 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

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

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

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

  • 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![/I]

    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