|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Recognizing a function name without the Database and OwnerI created a function as: Create Function u_fnSRWorkDayCalc
And then used in a stored procedure, however, the only way I could get the stored procedure to recognize it was to code it with the Database and Owner: SvcReports.dbo.u_fnSRWorkDayCalc. I would like to port the stored procedure to 15 other databases and would like to keep the same program without going in and changing SvcReports (the database name) for each stored procedure. Is there a way to create a function which does not need to be referenced like this ? I tried Create Function Public etc, but that didn't work. The owner (a.k.a. schema) is required by the SQL parser for function
references. However, the database name qualifier is optional as long as the stored procedures and function reside in the same database. If the objects are in different databases, 3-part names are needed to use the function. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "rmcompute" <rmcomp***@discussions.microsoft.com> wrote in message news:2AC2D48C-3324-48A0-A220-EF1965344590@microsoft.com... >I created a function as: Create Function u_fnSRWorkDayCalc > And then used in a stored procedure, however, the only way I could get the > stored procedure to recognize it was to code it with the Database and > Owner: > SvcReports.dbo.u_fnSRWorkDayCalc. I would like to port the stored > procedure > to 15 other databases and would like to keep the same program without > going > in and changing SvcReports (the database name) for each stored procedure. > Is > there a way to create a function which does not need to be referenced like > this ? I tried Create Function Public etc, but that didn't work. > > > You can do without the database name if the function resides in the same
database, but you need the owner name for a scalar function. You don't need the owner name either for a table valued function though. rmcompute wrote:
> I created a function as: Create Function u_fnSRWorkDayCalc As others have replied, the database name is optional, but the owner is > And then used in a stored procedure, however, the only way I could get the > stored procedure to recognize it was to code it with the Database and Owner: > SvcReports.dbo.u_fnSRWorkDayCalc. I would like to port the stored procedure > to 15 other databases and would like to keep the same program without going > in and changing SvcReports (the database name) for each stored procedure. Is > there a way to create a function which does not need to be referenced like > this ? I tried Create Function Public etc, but that didn't work. > > > required. Also, using schema-qualified object names is a GOOD habit to learn, it can help avoid some very frustrating debug situations.  |
|||||||||||||||||||||||
Other interesting topics