April 28, 2008 at 6:23 am
how function parameters other databasename (@dbname) use ?
--- example 1 --- (syntax error)
CREATE FUNCTION fnGetKur(@date datetime,@exchange varchar(5),@type tinyint,@dbname varchar(30)) RETURNS float AS
BEGIN
DECLARE @rate float
set @rate=0
select @rate=K.TUTAR1 from @dbname.dbo.KUR K where K.D=@exchange and K.TARIH=(select max(K1.TARIH) from @dbname.dbo.KUR K1 where K1.D=@exchange and K1.TARIH <=@date)
return @rate
end
----- example 2 --- (exec error)
CREATE FUNCTION fnGetKur(@date datetime,@exchange varchar(5),@type tinyint,@dbname varchar(30)) RETURNS float AS
BEGIN
DECLARE @rate float ,@Sql varchar
set @rate=0
select @sql='select @rate=K.TUTAR1 from ' + @dbname +'.dbo.KUR K where K.D='+ @exchange +' and K.TARIH=(select max(K1.DATE) from ' + @dbname+'.dbo.KUR K1 where K1.D='+ @exchange + ' and K1.DATE <='+ @date +')'
EXEC(@Sql)
return @rate
end
April 28, 2008 at 6:37 am
Example 1 fails because you can't have a variable setting the database name. Example 2 fails because the dynamic SQL that you use executes in a different context from the one that calls it. This means that the value of @rate will not be available to the calling procedure. You can get round this by using sp_executesql with an output parameter, instead of EXEC. Actually, I've a feeling that you can't use dynamic SQL at all inside a function definition. If I'm right about that then you could try rewriting your function as a stored procedure or, better still, review the design of your database(s). Perhaps you could consolidate all your tables to the same database?
John
April 28, 2008 at 8:03 am
thank you John,
but is not work
--- function example -- (executesql)
CREATE FUNCTION fnGetFexc(@exchange varchar(5),@dbname varchar(30)) RETURNS float AS
BEGIN
DECLARE @rate float ,@Sql varchar
set @rate=0
set @sql=N'select top 1 @rate=T1 from ' + @dbname +'.dbo.FEXC where D='+ @exchange +' and DATE<=getdate() order by TARIH desc'
exec sp_executesql @sql
return @rate
end
-- function uses---
select dbo.fnGetFexc('Eur','General')
-- error--
Only functions and extended stored procedures can be executed from within a function.
where is error ??
function parameters is database name uses example please..
thank you..
April 28, 2008 at 8:11 am
As I said, if you can't change your function to a stored procedure then you're going to have to change the design of your database. Perhaps you could create a Rates table, which you could keep up to date with triggers on the FEXC table in databases such as General. Not ideal, I know, but I'm not familiar with your system. Sounds like you might need professional help with this one.
John
April 28, 2008 at 8:27 am
thank you john
April 28, 2008 at 8:35 am
If you only have a finite number of pre-defined databases, you could use an IF statement to accomplish this.
CREATE FUNCTION fnGetKur(@date datetime,@exchange varchar(5),@type tinyint,@dbname varchar(30)) RETURNS float AS
BEGIN
DECLARE @rate float
set @rate=0
if @dbname = 'Database1'
select @rate=K.TUTAR1 from database1.dbo.KUR K where K.D=@exchange and K.TARIH=(select max(K1.TARIH) from database1.dbo.KUR K1 where K1.D=@exchange and K1.TARIH <=@date)
else
if @dbname = 'Database2'
select @rate=K.TUTAR1 from database2.dbo.KUR K where K.D=@exchange and K.TARIH=(select max(K1.TARIH) from database2.dbo.KUR K1 where K1.D=@exchange and K1.TARIH <=@date)
else
... (Database3, etc.) ...
return @rate
end
The parts I modified are bold.
That won't work if you're constantly adding new databases, or anything like that. But if you have a few databases, it might do the job.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2008 at 8:48 am
thank you ,
but, may be 1000 database in SQL Server and database name i don't know...
date is parameter use, but databasename is not parameters use..
April 28, 2008 at 9:31 am
You can't do Dynamic SQL in Transact-SQL functions.
You CAN in CLR functions.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 28, 2008 at 11:17 am
teo_erdem (4/28/2008)
thank you ,but, may be 1000 database in SQL Server and database name i don't know...
date is parameter use, but databasename is not parameters use..
I don't understand your second sentence there, so can't help with that.
On using the database name as a parameter with 1000 databases, you definitely can't use a standard SQL function for that. A stored procedure using dynamic SQL, or a CLR UDF, will work. I'd recommend a stored procedure, unless you really need to join to the data in another function.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2008 at 1:55 am
example - sql query -
select price as Price,dbo.fnGetFExc(getdate(),'US$',0,'General') as FExc, price*dbo.fnGetFExc(getdate(),'US$',0,'General') as F.Price
from sales
function is select column, due to i need function use (database name variable)
thank you..
April 30, 2008 at 1:58 am
example - sql query -
select price as Price,dbo.fnGetFExc(getdate(),'US$',0,'General') as FExc, price*dbo.fnGetFExc(getdate(),'US$',0,'General') as F.Price
from sales
function is select column, due to i need function use (database name variable)
thank you..
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply