Lookup UDF

Hi,
Can anybody provide me a Lookup UDF? I need to supply columnname,Tablename and condition dynamically and I need the scalar value in return.
Any help will be greatly appreciated...Hello again Rudra - I very recently commented on you not being around much these days :)

I have a suspicion about what you want to do and it is probably bad and likely impossible if interpreted literally. Do you want to supply a column name, table name, a key value to a function and get an alternate key value in return (i.e. have some dynamic SQL within the UDF)? If not please can you give more details about what you want this function to actually do.|||Hello again Rudra - I very recently commented on you not being around much these days :)

If not please can you give more details about what you want this function to actually do.

Thanks Pootle.:p It seems just like the old days.
Yea ,I was not around for a long time.But I always find the Yak Corral's B-E-A-U-T -Y Bcoz it always goes on and on and on...

Well,I know what I am trying to do is quite impossible but can you suggest me a way by which I can fetch a value in by lookup UDF or something like that in a long script?
In one of the script there are no of cases where things are done in this way-
case when Policy.FinanceID = 0 Then ' ' else 'Finance Co.: ' + (select name from Finance where financeId= policy.financeid)Can I use something else for that line?|||In one of the script there is no of cases where things are done in this way-
case when Policy.FinanceID = 0 Then ' ' else 'Finance Co.: ' + (select name from Finance where financeId= policy.financeid)Can I use something else for that line?Well, not knowing any more about your problem, the easiest thing would be to insert the data into the table.

INSERT Finance (financeId, name)
SELECT 0, ''
This also covers a bug bear of mine which is sticking your data into code. (linky: http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx) Otherwise you could create a view:

CREATE VIEW finance_x
AS
SELECT financeId, name
FROM finance
UNION ALL
SELECT 0, ''
and use that.|||Hmm... that link was great :cool: thanks a lot Pootle...see u soon.

looking to tune query...

If anyone is able to provide advice for tuning the below query in sql
server, it is much appreciated. In addition any index suggestions are
also appreciated as I have access to the tables. Thank you.

select a.id, isnull(b.advisement_satisfaction_yes, 0) as
advisement_satisfaction_yes,
isnull(c.advisement_satisfaction_no, 0) as advisement_satisfaction_no,
case
when isnull(b.advisement_satisfaction_yes, 0) >
isnull(c.advisement_satisfaction_no, 0) then 'YES'
when isnull(b.advisement_satisfaction_yes, 0) <
isnull(c.advisement_satisfaction_no, 0) then 'NO'
when isnull(b.advisement_satisfaction_yes, 0) =
isnull(c.advisement_satisfaction_no, 0) then 'TIE'
end as Satisfied_With_Advisement

from a
left Join

(select id, count(answer_text) as Advisement_Satisfaction_yes from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'yes'
GROUP BY id) b

on a.id = b.id

left join

(select id, count(answer_text) as Advisement_Satisfaction_NO from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'NO'
GROUP BY id) c

on a.id = b.id

where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:

SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x

Razvan|||AMAZING...THANK YOU SO MUCH!

Razvan Socol wrote:

Quote:

Originally Posted by

The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:
>
SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x
>
Razvan

Looking for references for querying Active Directory (AD) through SQL Server

Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.

Thanks.

There are a lot of examples out there:

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx
http://support.microsoft.com/kb/299410

http://blogs.msdn.com/ikovalenko/archive/2007/03/22/how-to-avoid-1000-rows-limitation-when-querying-active-directory-ad-from-sql-2005-with-using-custom-code.aspx

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Looking for DTS example

Hi all,
I am trying to automate calling a SQL Copy Data task. I need to provide the
source database dynamically at the least. I am using MSDE in a C3 program.
Does anyone have any examples or sites they can point me to? I am at a loss
after much searching.
ThanksMe too.
Apparently exporting a DTS to VB gives you the option of actually
controlling the DTS batch more, but you need Visual Basic installed (dunno
if it'll run in a VBA environment).
I've tried a DTS storage file & metadata services, the DTS storage file is
in binary and I can't get metadata services working just now...
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"S" <spamaway@.hotmail.com> wrote in message
news:OG5F71TqDHA.1408@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am trying to automate calling a SQL Copy Data task. I need to provide
the
> source database dynamically at the least. I am using MSDE in a C3
program.
> Does anyone have any examples or sites they can point me to? I am at a
loss
> after much searching.
> Thanks
>