Search code examples
sqlsql-serverstored-proceduresudf

SQL Server stored procedure, creating function

I'm currently trying to create a function in a database that was created in a stored procedure.

Set @sql = 'USE [' + @dbname + '] GO CREATE FUNCTION dbo.functionname(@trajectid int)
    RETURNS int
    AS
    BEGIN   
        DECLARE @result int;
        (SELECT @result = SUM(duur) FROM AgendaItems WHERE trajectid = @trajectid)
        RETURN  @result
    END'
exec(@sql)

What we want to achieve is using the function in the table definitions (also in stored procedures)

gebruikt AS [dbo].functionname([id]),

We tried using Maindatabase.dbo.functionname, which returned an error:

A user-defined function name cannot be prefixed with a database name in this context.

Thanks in advance for any help.

Solution

  • Sorry for being straight but, you simply should not use a stored procedure to create DDL - and in fact, the system is preventing you from doing that, as it's really a bad practice.

    There are workarounds, but you should really change the way you are handling the process that you want to create - that would be the only real solution