December 16, 2011 at 11:01 am
Hello,
I found that previous programer created Scalar valued fauncion on a seperate database that stored procedure is running and we are not using this db anymore,is there reason that this function needs to run on seperate db or I can move this function to the same database where stored procedure is running.
Thank you
December 16, 2011 at 11:26 am
December 16, 2011 at 12:35 pm
Krasavita (12/16/2011)
Hello,I found that previous programer created Scalar valued fauncion on a seperate database that stored procedure is running and we are not using this db anymore,is there reason that this function needs to run on seperate db or I can move this function to the same database where stored procedure is running.
Thank you
That completely depends on what the function does. Does it require data from the database it is created in? I suspect that it does but only you can answer that. Other than the actual contents of the function there is no reason it has to be on any given database. I would be surprised if you can just simply run the create statement for this function on another database unchanged.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 16, 2011 at 12:38 pm
also keep in mind, scalar functions come with a performance hit for sqlserver !
So, while you're at it, because you'll have to modify all referencing queries (views, sprocs, other functions, application code, did i miss something) , convert it to an inline table value function ! (yes, even it if only returns a single column in a single row)
Then cross apply it with your query and test it.
so
select ..., otherdb.schema.ScalarFn ( T.mycol ) as Whatever
from yourschema.yourtable T
...
will become
select ..., ITVF.colname as Whatever
from yourschema.yourtable T
cross apply schema.InlineTableValuedFn ( T.mycol ) ITVF
...
SQLServer , like any RDBMS, is made to handle sets, and it does that way better than it handles single values
edited: FYI another cross/outer apply reference http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-advent-2011-day-16
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 20, 2011 at 8:52 am
I rec reated function under other database without any problems.
Here is what function what is doing:
[dbo].[Case](@string varchar(8000)) returns varchar(8000) as
begin
set @string = lower(@string)
declare @i int
set @i = ascii('a')
while @i <= ascii('z')
begin
set @string = replace( @string, ' ' + char(@i), ' ' + char(@i-32))
set @i = @i + 1
end
set @string = char(ascii(left(@string, 1))-32) + right(@string, len(@string)-1)
return @string
end
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply