November 21, 2010 at 10:07 pm
Hi All
I wish to keep a function in one database and plan to call ir from different database. For that, I have to pass database name as parameter but I couldn't figure a direct way to use this passed parameter. I dont want to get in complexity of making batch files or extended stored proc for this simple task. Here is what I tried:
alter function dbo.fn1(@db varchar(50))
returns int
as
begin
declare @id int
set @id=3
declare @STR varchar(100)
set @STR='select col1 from '+ @db+'.dbo.test1 where col1=2'
exec(@str)
return @id
end
Any thoughts how this could be accomplished?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
November 22, 2010 at 1:01 am
Heh... yes... ask an "SQL Server DBA". 😉
--Jeff Moden
November 22, 2010 at 1:15 am
User defined functions have some limitations. One of those limitations is that you can’t use dynamic SQL in it. This is by design and there is no way around it. You’ll have to use a different approach.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2010 at 1:30 am
No You can't use the dynamic sql in the functions
November 22, 2010 at 2:03 am
Alternatively, u can write proc rather than function or include string initialization in proc and call the proc from inside sql function.... dont know whether it will work for u ... as per ur business needs.......
November 22, 2010 at 2:12 am
I dont think you can call a proc from inside a function except extended procs.
So it concludes that there is no simple work around expect if I can find that "SQL Server DBA" who can answer it, right RBAR?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
November 22, 2010 at 6:03 am
S_Kumar_S (11/22/2010)
I dont think you can call a proc from inside a function except extended procs.So it concludes that there is no simple work around expect if I can find that "SQL Server DBA" who can answer it, right RBAR?
My point was that if you're going to tout "SQL Server DBA" in your signature line, you should already know some of the basics. 😉 It's not a title to be taken lightly. One of the basics is that you can't execute dynamic SQL in a function. The only way (without writing a CLR or Extended SProc or resorting to embedded code in a GUI) you're going to be able to pull this off is to use some form dynamic SQL in a stored procedure.
--Jeff Moden
November 22, 2010 at 6:10 am
if you take a proc, stick it in the master database, and mark it as a system object, then it will use the datasase reference of the calling SQL.(it also neeeds to start with sp_)
so for example, if you have a procedure setup as above,that does select * from sys.objects when your db context is db_name() = SandBox ,
it selects from SandBox.sys.objects.
switch contexts, and it might select from Production.sys.objects.
--EXECUTE sp_ms_marksystemobject 'sp_find'
CREATE procedure [dbo].[sp_find2]
@findcolumn varchar(50)
AS
BEGIN
SET NOCOUNT ON
SELECT
TableFound,
ColumnFound
FROM
(
SELECT
1 AS SortOrder,
sys.objects.name AS TableFound,
'' AS ColumnFound
FROM sys.objects
WHERE sys.objects.type IN('U' ,'V')
AND sys.objects.name LIKE '%' + @findcolumn + '%'
UNION ALL
SELECT
2 AS SortOrder,
sys.objects.name AS TableFound,
sys.columns.name AS ColumnFound
FROM sys.objects
INNER JOIN sys.columns ON sys.objects.object_id=sys.columns.object_id
WHERE sys.objects.type IN('U' ,'V')
AND sys.columns.name like '%' + @findcolumn + '%'
) X
ORDER BY
SortOrder,
TableFound,
ColumnFound
END
Lowell
November 22, 2010 at 10:06 pm
Thanks For reply Lowell. I know we can use sp_ in any database context, but I had to use that function in a select statement(select clause), so this probably won't work.
Anyway, no problem. I'll put the function in all databases where i plan to call it.
But it makes to my wish list for any future sql server releases.
Thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
November 22, 2010 at 10:09 pm
Well Jeff, I didn't ask if we can execute a dynamic sql from function, I asked if there is a work around for it. i knew there is no direct way to do it.
And I am not touting anything here, this is what I am since last 5 years "sql server DBA".
Not sure what made you think that I am taking it lightly.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
November 22, 2010 at 10:18 pm
S_Kumar_S (11/22/2010)
Thanks For reply Lowell. I know we can use sp_ in any database context, but I had to use that function in a select statement(select clause), so this probably won't work.Anyway, no problem. I'll put the function in all databases where i plan to call it.
But it makes to my wish list for any future sql server releases.
Thanks
i guess you missed my point; as long as your function does not use dynamic sql, ie, looking for a specific table, the technique i described will work.
make your function start with fn_ or sp_, mark it as a system object, and it will work. my example was a proc, but it works with views, functions, everything.
Lowell
November 22, 2010 at 11:41 pm
Hi Lowell
I indeed missed your point. But when I tried as you told, I still can't make it working. I was able to create a funtion that starts with fn_ in master database and marked it system object using sp_ms_marksystemobject. So this funtion is added to "system funtions" folder of master database. Also when I query sys.objects, I can see that IS_MS_SHIPPED=1 for this funtion. So i though that i can call it now from any database, but i was wrong . it didnt work. same thing works for proc but not for funtion. pls see below the code i tried. let me know if I missed something.
USE master
GO
create proc sp_p1
as
select 1
GO
EXECUTE sp_ms_marksystemobject 'sp_p1'
create function dbo.fn_alwayswork()
returns int
as
begin
declare @id int
set @id=3
return @id
end
GO
EXECUTE sp_ms_marksystemobject 'fn_alwayswork'
GO
Use mydb
go
select dbo.fn_alwayswork() --doesn't works
sp_p1--works
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
November 23, 2010 at 1:22 am
U could write a proc instead of writing function with the same code.... call the proc 1st.. this proc should store the output in temp table.. then create a function which will get the values from the temp table....
I could find this way only to get the values in '''select statement'''........
November 23, 2010 at 7:54 pm
S_Kumar_S (11/22/2010)
Well Jeff, I didn't ask if we can execute a dynamic sql from function, I asked if there is a work around for it. i knew there is no direct way to do it.And I am not touting anything here, this is what I am since last 5 years "sql server DBA".
Not sure what made you think that I am taking it lightly.
BWAA-HAA!!! Show me where you asked for a work-around, please... 😉
S_Kumar_S (11/21/2010)
Hi AllI wish to keep a function in one database and plan to call ir from different database. For that, I have to pass database name as parameter but I couldn't figure a direct way to use this passed parameter. I dont want to get in complexity of making batch files or extended stored proc for this simple task. Here is what I tried:
alter function dbo.fn1(@db varchar(50))
returns int
as
begin
declare @id int
set @id=3
declare @STR varchar(100)
set @STR='select col1 from '+ @db+'.dbo.test1 where col1=2'
exec(@str)
return @id
end
Any thoughts how this could be accomplished?
Anyhow... I believe the only work-around here is like I said... you'll need to use a stored proc with dynamic SQL. I don't believe there's a way around that.
--Jeff Moden
November 23, 2010 at 7:58 pm
sumit.agrawal (11/23/2010)
this proc should store the output in temp table.. then create a function which will get the values from the temp table....
Just to be clear, one can no more call on a Temp Table from a function than you can run dynamic SQL from a function. If what you're calling a "Temp Table" is actually a permanent table with "temporary" rows, then maybe but you'll really need to watch out for concurrency issues both in the proc and in the function.
I guess my question would be... what is the advantage of first having to run a proc and then select from a function? Why not just return a result set from the proc?
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply