September 21, 2007 at 10:04 am
Hi, I have a select statement accessing data through a linked server using the OpenQuery syntax. The statement will never return more than one row and I need to utilize this logic many times within a given Select statement. In an effort to minimize duplicating/repeating this code I built the statement as a scalar udf but then learned that dynamic sql (which I need to utilize to pass a parameter value to my OpenQuery statement) isn't allowed within a function.
What I wanted to achieve was something like the following...
Select a.col1, a.col2, myudf(a.col3), a.col4, myudf(a.col5),myudf(a.col6), myudf(a.col7) from tablename a
I would appreciate any thoughts/suggestions on how to accomplish this task without repeating the code I had hoped to include within the scalar udf.
TIA, Chris.
September 21, 2007 at 10:16 am
it really depends on what your UDF is doing;
we need that code to determine whether this can be done cleaner; the more real details you post, the better we can help; theoretical examples tend to muddy the waters a bit.
from the looks of it it might be getting a specific value, like a min or max, so you could do the same thing with a GROUP BY, or several self joins on the tables most likely.
Select a.col1, a.col2, MAX(a.col3), a.col4, MAX(a.col5),MAX(a.col6), MAX(a.col7) from tablename a GROUP BY a.col1,a.col2,a.col4
Select a.col1, a.col2, b.col3, a.col4, b.col5,b.col6, b.col7 from tablename a
inner join tablename b on a.col1 = b.col1 AND b.somefield = 3
GROUP BY a.col1,a.col2,a.col4
Lowell
September 21, 2007 at 12:17 pm
Hi, I've included the code I was hoping to encapsulate within a UDF. It's basically just returning a user name for a parameter supplied account name (example input='cworthi' ; output='chris worthington').
I have select statements contaning numerous account name columns that need to be translated in this fashion.
Select acctnm1, acctnm2, acctnm3, acctnm4, acctnm5, acctnm6 from dbo.log
I had hoped to achive this with a UDF something like this...
Select myudf(acctnm1), myudf(acctnm2), myudf(acctnm3) etc...
I'm trying to avoid coding an inner join to the same table (i.e. the OpenQuery syntax in the UDF shown below) for each acctnm column in the column list.
Maybe I should just create a view using the OpenQuery shown below and then join my log table (albeit 6 times using my example from above) to the view. At least I would then only have one place in which to maintain the OpenQuery syntax shown below????
USE [xxxxxxxxxxxx]
GO
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER OFF
GO
CREATE
FUNCTION [dbo].[ufnGetCommonName]
(
@SamAccountName varchar(20) )
RETURNS
varchar(64)
AS
BEGIN
DECLARE
@CommonName varchar(64)
DECLARE
@OpenQuery varchar(60)
DECLARE
@tSQL varchar(400)
SET
@OpenQuery = "Select CN from OpenQuery(ADSI,"
SET
@tSQL = "'Select CN From "
+ " ''LDAP://DEVXYZ/ou=abcdefg,DC=DEVab,DC=hijk''"
+ " Where objectClass = ''User'' And objectClass <> ''Computer''"
+ " And SamAccountName = ''"
+ @SamAccountName
+ "''') AS AD"
EXEC (@OpenQuery+@tSQL)
RETURN
--@CommonName
END
September 21, 2007 at 1:22 pm
Chris - I don't think you want to use a function for this, at least not to return a scalar value. If you have 7 columns per record, and 1000 records, you'd be calling the OPENQUERY syntax 7000. Each call is going to open a path into the external database, and let's just say that's a HUGE penalty.
Recommendation - Assuming the results from the LDAP query aren't ludicrously large (like, say, millions of rows), skip the function altogether, and for that matter, skip the dynamic SQL. Instead - run an OPENQUERY to populate/refresh a table, and use the tmp table to do your lookups. something like:
Create table #tmpldap (AD varchar(100), CN nvarchar(200))
insert #tmpldap (ad, cn)
Select * from OPENQUERY(ADSI,'select SAMAcctName, CN from ''LDAP://DEVXYZ/ou=abcdefg,DC=DEVab,DC=hijk'' Where objectClass = ''User'' And objectClass <> ''Computer''')
create index tmpldap_idx on #tmpldap(ad)
--your results
select ld1.cn as acctnm1_full,
ld2.cn as acctnm2_full,
ld3.cn as acctnm3_full, (etc...)
from
<table> t
left join #tmpldap ld1 on t.acctnm1=ld1.ad
left join #tmpldap ld2 on t.acctnm1=ld2.ad
left join #tmpldap ld3 on t.acctnm1=ld3.ad
--dispose of the temp table and index
drop index tmpldap_idx
drop table #tmpldap
Not incredibly pretty either, but should be a VAST improvement if your table has numerous rows and columns as you mentioned.
Finally - for something used this much, you might consider having a SQL table containing all of the users pulled from the LDAP query, and update that table on occasion (so you don't have to rebuild the temp table for each query needing it).
----------------------------------------------------------------------------------
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?
September 21, 2007 at 2:57 pm
Sounds reasonable...thanks for the advice/suggestion!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply