May 10, 2007 at 9:17 pm
Need some help from the pros. I am probably over-thinking this and making it more complicated than necessary.
I am trying to write a TSQL function that accepts a username as the input, does an OpenQuery to LDAP to check to see if the Email attribute is populated, and returns a 0 or a 1 depending upon the value of the email attribute.
1st problem I hit is not being able to do dynamic sql inside a function.
So I created a procedure and called it from the function, only to get another error: "Only functions and extended stored procedures can be executed from within a function."
So now I'm turning to you guys. Is there an easy solution?
Here is what I envisioned my function to look like if functions could have dynamic SQL:
Create function aftest
(@username varchar (10))
RETURNS smallint
as
BEGIN
DECLARE
@sql varchar (500),
@returncode smallint,
@rowcount smallint
SELECT @sql =
'Select
FROM
OpenQuery(ADSI, ''SELECT mail FROM ''''LDAP://DC=company,DC=com''''
where samaccountname = ''''' + @username + ''''''')'
exec(@sql)
Select @rowcount = @@rowcount
IF @rowcount = 0
SELECT @returncode = 0
ELSE
SELECT @returncode = 1
RETURN(@returncode)
END
May 10, 2007 at 10:52 pm
Why you decided you need function?
What's wrong with procedures?
----------------------
Create procedure aftest
@username varchar (10),
@EmailExists bit OUTPUT
as
DECLARE
@sql varchar (500)
SELECT @sql =
'Select
FROM
OpenQuery(ADSI, ''SELECT mail FROM ''''LDAP://DC=company,DC=com''''
where samaccountname = ''''' + @username + ''''''')'
exec(@sql)
Select @EmailExists = SIGN(@@rowcount)
GO
-------
_____________
Code for TallyGenerator
May 11, 2007 at 8:04 am
Thanks Sergiy. Short and simple - I like it.
I was hoping to create a function, however, because I have a need to produce a report of all the invalid email addresses in a table, and I was trying to avoid using a cursor. If I had a function that did the validation my report syntax could be something like:
Select emailaddress
from table1
where aftest(emailaddress) = 0
If using a procedure, wouldn't I have to build a cursor of all the email addresses and call the procedure for every row?
May 11, 2007 at 8:23 am
I'm afraid LDAP lookup times slower than retrieving next row from recordset.
So, using cursor or WHILE loop not gonna slow down your procedure.
Probably you need to revise the whole design idea in order to make it fast.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply