April 23, 2012 at 2:17 am
I need to access different databases from a single function how can i do it with dynamic function.
if it is not possible kindly suggest other alternatives.
April 23, 2012 at 2:30 am
You provided very little information. What exactly you want to do?
======================================
Blog: www.irohitable.com
April 23, 2012 at 3:08 am
Hi
thank u for ur reply.
Actually we are using multiple database in our application for a single application .
i need acess different database tables and return a value from a function .
where i have to pass table name and database name as a parameter .
April 23, 2012 at 3:17 am
You can certainly reference other databases and tables within a function providing the permissions are in place.
Remember though, UDF's within a SELECT statement have to run on every row returned within the SELECT statement which may affect your performance, or the performance of the databases you are referencing.
That aside, you can create a single function passing the database and table name as parameters; within the function use something along the line of:
IF @DatabaseName='x' and @TableName = 'Y'
BEGIN
-- do you TSQL here
END
etc
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 23, 2012 at 3:24 am
Thank u Robin
I cant use if else statement because there will be a dynamic database creation
and i need acess the function using dynamic sql statement or alternative where
i will use a single function for all databases.
April 23, 2012 at 3:37 am
Personally, I try and avoid dynamic SQL but understand it has it's place.
Unfortunately, I believe you cannot execute dynamic SQL in a function (via sp_executesql).
You could explore the creation of a .NET CLR to accomplish this but you'll be adding another layer of complexity.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 23, 2012 at 4:30 am
what will be the complexity in CLR functions ....what will be the alternative for this ....
April 23, 2012 at 4:34 am
That's a a very big question as it covers so many facets! Only you as the developer and your business colleagues can decide if CLR is suitable for your environment.
A quick Google search turns up a starting point for your question:
http://msdn.microsoft.com/en-us/library/ms254498.aspx
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 23, 2012 at 5:12 am
April 23, 2012 at 5:19 am
This my code
Create FUNCTION [dbo].[UFN_GET_URL]
(
@databasename navarchar(20), -- dynamically will pass the database name
@tablename navarchar(20), --dynamically will pass the tablename
@SubMenuID int,
@Flag tinyint
)
RETURNS nvarchar(300)
AS
BEGIN
declare @Url nvarchar(300)
if(@Flag=1) -- Get URl For View
Begin
select @Url=(select Url
From @databasename..@tablename s
inner join UserGroupLine l on l.refMenuID=s.RefMenuID
where RefSubMenuID=@SubMenuID and NewEdit=1 and (IsEdit=1 or IsView=1)
)
if @Url is null
begin
set @Url=''
end
End
return @Url
END
can u send example function if possible.....
April 23, 2012 at 5:22 am
You can't do that in a function. Only way to have variable database and table names is dynamic SQL, and functions cannot have dynamic SQL. Consider a stored procedure instead?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2012 at 5:42 am
hi Vinu Vijayan,
Thank u for ur reply can u send me a example function on how to use an dynamic sql query in function
April 23, 2012 at 5:45 am
You cannot use dynamic SQL in a function. (well there's a rather dangerous workaround, but it is strongly recommended against)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2012 at 11:46 am
What you are trying to do points out a fatal flaw in the design. If you want to make things dynamic in terms of which database your application is connecting to, then do that in your application, e.g. by changing the database context of the connection based on the caller.
If you really want to use database code to achieve the abstraction then you could create a multi-tenant database where the "tenant" is identified by a column in your tables, and that identifier would be passed into each of your functions and procedures.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 24, 2012 at 12:12 am
Nope you can't do what you are trying to do in a UDF....by Dynamic UDF I meant the passing of parameters and the returning of values...which is done dynamically.
But, you can't use Dynamic SQL in a UDF. You can't use Exec() in a function.
You'll have to use a Stored Procedure for that. Since you misunderstood me and may be I was the one who pointed you in the wrong direction...I did a little work to convert your UDF into a Stored Procedure.
Hope this helps you.
--Creating Procedure
Create Procedure [dbo].[UFN_GET_URL]
(
@databasename nvarchar(20), -- dynamically will pass the database name
@tablename nvarchar(20), --dynamically will pass the tablename
@SubMenuID int,
@Flag tinyint
)
AS
BEGIN
declare @query varchar(max)
declare @Url table(url nvarchar(max) )
If(@Flag=1) -- Get URl For View
Begin
Set @query = 'select Url From'+@databasename+'..'+@tablename+' s inner join UserGroupLine l
on l.refMenuID=s.RefMenuID
where RefSubMenuID=@SubMenuID and NewEdit=1 and (IsEdit=1 or IsView=1)'
Print @query
insert into @Url exec(@query)
If ((Select * From @Url) is null)
begin
Insert Into @Url values('')
End
End
Select * From @Url
End
--Executing Procedure
Exec [dbo].[UFN_GET_URL] 'Test', 'Test', 12, 1
Viewing 15 posts - 1 through 15 (of 53 total)
You must be logged in to reply to this topic. Login to reply