We help IT Professionals succeed at work.

TSQL - functions..


SQL Server 2008.

Can a function be declared in a stored procedure?

I just want to simplfiy the code and make it more readable.  

Watch Question

A function can be called in a stored procedure, but I suspect what you are really asking about is something more like a sub-routine.  If so, there is no elegant way to do this.  I wish there was!


yes i want to declare it and call it in the stored procedure.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
You could use dynamic SQL to recreate a udf from within a stored proc.  I assume you would then be able to call/invoke the udf later in the same proc.
It can, however I tend to think a few parameters in the function could maybe prevent the need to do so.

There are some problems

1. While you create the UDF in your procedure (dynamic SQL) you can only call it in dynamic sql
2. Don't forget to test for existance and drop it
3. If procedure is called by several processes at same time you can have trouble (locking or else)
4. The login must have permission to create a function

3 and 4 are giving me a bad feeling

IF OBJECT_ID ( 'dbo.testproc', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.testproc;
CREATE PROCEDURE dbo.testproc 
    DECLARE @cmd  as nvarchar(4000);
    SET @cmd = '
if ... drop function
    exec @cmd

    set @cmd = ' use the function here'
    exec @cmd 

Open in new window

If I have understand the issue correctly, you wanted to use the function inside an stored procedure rather than writing the same code again and again. If its true, then create the function and simply call it in your stored procedure.
     If not, then ScottPletcher\Jogos already showed you the way.

For the record : declaring a function in dynamic sql and using it in dynamic sql does not make your code more readable.

We don't have a clue on what that function has to do so we can't suggest on how to do it another way.
For example
I think #tables or table variables is a way to deal with 'variable circomstances' in a process when it comes to which list of values applie on this moment.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Actually, if you look more closely, you'll see that I was careful enough to say *RE*create a function.  The function would need to already exist, at least as a shell, but could then be replaced by code generated dynamically.

Yes, you would need to do your own serialization in that case, but that's not terribly difficult, viz sp_getapplock & sp_releaseapplock.  
Top Expert 2011
there are no "inline sub routines" within t-sql    

you can put common code into a separate stored procedure and execute that

you can put the code into a while loop
and cursor/loop through it

you could use goto and labels  
but that leads to spaghetti

if you have a common , significant requirement
then either put it into its own procedure/function
redesign the process to deal with the requirement in a more set based manner
(ie put the variables into a table  and process it in "one go" via a select/cursor/while loop,,,,)

of course it maybe that your common code could be addressed via a view, or cte?

can you be more specific over the actual situation?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.