DlookUp Function in SQL2000 User Defined Function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DlookUp Function in SQL2000 User Defined Function

Author

Topic

jeff
Starting Member

6 Posts

Posted - 2003-04-02 : 15:30:04

Hi,

Im new in writing Stored Procedures and/or User Defined Functions in SQL2000, Since a SQL2000 Database includes "User Defined Functions" that can be used in queries, can you help me write a SQL2000 User Defined Function that works exactly like the DLookUp function of Access97?
The parameters that can be pass to this user defined function are Fieldname, Tablename, and Criteria.

ie: myDLookUp(strID, strTableName, strCriteria)

Thanks in advance.
Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-02 : 15:37:22

Well what have you written so far?

Tara

jeff
Starting Member

6 Posts

Posted - 2003-04-02 : 15:49:28

Hi Tara,

Can you help me to have the below function working?

Thanks Again,
Jeff

CREATE FUNCTION myDLookUp
(
@strFld varchar(100),
@strTable varchar(100),
@strCrit varchar(255)
)
RETURNS
varchar(100)
AS
BEGIN
Declare @RetVal varchar(100)
Declare @strSQL varchar(100)

IF @strFld Is not Null And @strTable Is not null
set @RetVal = (Select + @strFld + from + @strTable)
if @strFld Is Null And @strTable Is null
set @RetVal = Null

Return ( @RetVal )
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-02 : 15:55:11

Well just looking at the code, I see one problem:

set @RetVal = (Select + @strFld + from + @strTable)

should become:

set @RetVal = 'Select' + @strFld + 'from' + @strTable

I don't think that you need the parenthesis, but maybe you do. If so, then:

set @RetVal = '(Select' + @strFld + 'from' + @strTable + ')'

The point here is that to concatenate strings together, you need to enclose them in quotes, but you don't have to do that for the variables since they are already of character type.

HTH,

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-02 : 16:06:07

The other problem is that you can't query the database in the manner you are attempting within a user-defined function; you will need to use a stored procedure.

And as i always say, once people start writing procedures or function and have parameters for table and field names (along with accepting a parameter for criteria on that table), just use a SELECT statement to gather your data, not stored procs.

that is, even if the function did work,

Set @d = dbo.DlookUp("Field","Table","A > 1")

is not much eaiser to write than

set @d = (SELECT FIeld FROM TABLE WHERE A > 1)

And they are equivalent. Same for DSUM(), DMAX(), etc.

Just use the SELECT statement and you should be fine!

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-02 : 16:20:58

Jeff, good point. I didn't really read the code except to check for syntax problems.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-02 : 22:31:56

This function can be 'way' simplified - in SQL 2000, any NULL added to a string makes the entire string null, so do away with the local variables, SETs and IF conditions.

CREATE FUNCTION dbo.myDLookUp (
@strFld varchar(100),
@strTable varchar(100),
@strCrit varchar(255)
)
RETURNS
varchar(100)
AS
BEGIN
Return ( '(Select ' + @strFld + ' from ' + @strTable + ')' )
END

If you want to add a WHERE condition using strCrit

Return ( '(Select ' + @strFld + ' from ' + @strTable + ')' + IsNull(' WHERE ' + @strCrit, '') )

Sam

----------------------
The rule on staying alive as a forcaster is to give 'em a number or give 'em a date, but never give 'em both at once.
- Jane Bryant Quinn

jeff
Starting Member

6 Posts

Posted - 2003-04-03 : 15:53:32

Sam,

I tried the code of myDLookUp() that you wrote below and used it in a SELECT statement just like the DLookUp() when used in Access97 SELECT statement query but the function returns the SELECT statement string (as shown below) not the values that should be obtained when you execute that SQL statement.

I also tried to use a stored-procedure just like what jsmith8858 suggested and it works fine but you cannot use a stored-procedure as a source/table for another query that depends on the result of the stored procedure that was made.

myDlookUp function Result:
Select strCompanyName from tblARCustomer WHERE strCustomerID = 'CUST-10007'

Thanks Again,
Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources