Search code examples
sql-serverdatabasefunctionsqlcmd

How to create object(function) if database name is defined in SQLCMD mode variable?

My SQL looks like this:

:SETVAR VAR_DB_NAME DatabaseName.DBO

SELECT * FROM $(VAR_DB_NAME).tableName

If I try to do:

CREATE FUNCTION $(VAR_DB_NAME).FunctionName

I'm getting an error:

CREATE/ALTER FUNCTION' does not allow specifying the database name as a prefix to the object name

How can I check if function exists and create/drop it using same variable?

Solution

  • As the error says, you cannot include the database name in the CREATE statement. This works:

    :SETVAR VAR_DB_NAME DatabaseName
:SETVAR VAR_SCHEMA_NAME dbo

use $(VAR_DB_NAME)
go

create function $(VAR_SCHEMA_NAME).foo()
returns int
as 
begin
return (1)
end
go

    It would be better to use two variables here anyway: in my opinion, a variable called "DB_NAME" should not be (mis)used to include a schema name.