Create function inside procedure, vice versa? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Create function inside procedure, vice versa?

Author

Topic

pixelmeow
Starting Member

9 Posts

Posted - 2009-07-30 : 16:33:11

I'm trying to put together a file with scripts that I can use to create tables, views, procedures, and functions from one server to another. I also run these daily to refresh the data in this schema, because the INSERT_INTO procs look at another schema for what they do, which is prepare the data for reports.

The problem is that I have some functions for formatting names and such that I would like to create, also. The INSERT_INTO procs call the name formatting function. Is it possible to create a function from inside a procedure? Like:


USE [dbname];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE CREATE_fn_NAME_FORMATTING
AS
    CREATE FUNCTION fn_NAME_FORMATTING()
            (@FULLNAME varchar(50))
            RETURNS varchar(50)
            AS
            BEGIN
            DECLARE @NAME VARCHAR(50)
                SET @NAME = [STUFF]
            RETURN @NAME
            END
    EXEC DBO.FN_TESTING2
END
GO

This is just off the top of my head as an example. Below is what I want to be able to do, so I can just EXEC DBO.CREATE_ALL_STUFF.


USE [dbname];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[CREATE_ALL_STUFF]
AS
EXEC DBO.CREATE_A_TABLE
EXEC DBO.CREATE_C_TABLE
EXEC DBO.CREATE_E_TABLE
EXEC DBO.CREATE_P_TABLE
EXEC DBO.CREATE_S_TABLE
EXEC DBO.CREATE_vw_DSA_MILESTONE_REMARKS
EXEC DBO.CREATE_MILESTONE_REMARKS_TABLE
EXEC DBO.CREATE_CASES_ALL_TABLE
EXEC DBO.CREATE_fn_NAME_FORMATTING    -- <--- that's the one I want to create
EXEC DBO.INSERT_INTO_CASES_ALL
EXEC DBO.INSERT_INTO_MILESTONE_REMARKS
EXEC DBO.INSERT_INTO_A
EXEC DBO.INSERT_INTO_C
EXEC DBO.INSERT_INTO_E
EXEC DBO.INSERT_INTO_P
EXEC DBO.INSERT_INTO_S
EXEC DBO.CREATE_vw_A
EXEC DBO.CREATE_vw_C
EXEC DBO.CREATE_vw_E
EXEC DBO.CREATE_vw_P
EXEC DBO.CREATE_vw_S
GO

Is this possible?

pixelmeow
Starting Member

9 Posts

Posted - 2009-07-31 : 13:35:06

Oh well, it was worth a try. I'll see what I can do.

yonabout
Posting Yak Master

112 Posts

Posted - 2009-08-06 : 04:35:47

Hi,

I think you can do it writing the function as dynamic sql inside the stored procedure

I tried this (altered your original function a bit to get it to work on my db)

create PROCEDURE dbo.CREATE_fn_NAME_FORMATTING
AS

begin

declare @SQL varchar(5000)

set @SQL = 

'CREATE FUNCTION dbo.fn_NAME_FORMATTING(@FULLNAME varchar(50))
            RETURNS varchar(50)
            AS
            BEGIN
            DECLARE @NAME VARCHAR(50)
                SET @NAME = ''[STUFF]''
            RETURN @NAME
            END'

exec (@SQL)

end

Seems to work

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources