2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
killie01
  • 253
  • 3
  • 11
  • I'm suspect on "USE [ @dbname]". try to remove it ans add the dbname to your function and the tables inside it – FLICKER May 26 '16 at 17:28
  • And your function itself has some room for improvement. Scalar functions are notoriously poor from a performance standpoint. This could be converted to an inline table valued function quite easily. They are more flexible and it would perform better too. – Sean Lange May 26 '16 at 17:46
  • 4
    My bigger concern though is using a stored procedure to create a function. That screams of a poor design. A stored procedure should not be used to create ddl like this. – Sean Lange May 26 '16 at 17:47
  • @FLICKER The problem is that that would create the function in the main database where we create the new database from, not inside the new database. – killie01 May 26 '16 at 20:14
  • @SeanLange Lange, thank you! I'll be looking into that and changing it. Well the problem is, we cannot manually create them, as this needs to be an automated process. – killie01 May 26 '16 at 20:15
  • What's the automated process's goal? The error text is very clear - you can't write `gebruikt AS Maindatabase.[dbo].functionname([id])`, instead it should be `gebruikt AS [dbo].functionname([id])`. This means that this function should exist in the same database where the computed column is declared. – Zohar Peled May 29 '16 at 08:09
  • The stored procedure is in a main database where all shared data is stored. But by client's requirements we need to give every seperate company that registers, their own database. But it needs to happen automatically and we want to create some functions to calculate values for certain collumns. We create the collumns for that database from the stored procedure in the main database. We never want to manually put something into the clients database. – killie01 May 29 '16 at 09:54
  • @killie01 - this is best accomplished by tools _external_ to the child databases. How are you creating the databases in the first place? It's not like you can use a stored procedure inside B to create itself... Probably, I'd create a static database, including all the necessary procedures and views, then restore from/copy that, as if it were a VM image. Or if that's where they're stored, just spin up the VM image in the first place. There should be no reason to create a procedure this way. – Clockwork-Muse May 29 '16 at 10:39
  • @killie01, if I understand correctly your client databases have no tables, only functions and procedures that read data from tables in the "Main Database". Do you currently use views to expose table data to client applications? – Alex May 29 '16 at 11:08
  • I will be looking into the 'VM image' idea. I left this job to a colleague of mine who didnt think it was possible and tried it this way. A 'template' database would be preferred in this case if possible indeed. The performance would not be the biggest issue since it would not occur on a regular basis. – killie01 May 29 '16 at 20:46

1 Answers1

2

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

Mik1893
  • 317
  • 4
  • 14