We help IT Professionals succeed at work.

TSQL - functions..

Hej,

SQL Server 2008.

Can a function be declared in a stored procedure?

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

Thanks
Comment
Watch Question

CERTIFIED EXPERT
Commented:
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!
soozhCEO

Author

Commented:
yes i want to declare it and call it in the stored procedure.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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.
Commented:
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;
GO
CREATE PROCEDURE dbo.testproc 
AS 
    SET NOCOUNT ON;
    DECLARE @cmd  as nvarchar(4000);
    
    SET @cmd = '
if ... drop function
CREATE FUNCTION ....'
    exec @cmd

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

Open in new window

Commented:
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.

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.  
CERTIFIED EXPERT
Top Expert 2011
Commented:
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.