Hi all,

Access has a DLookup function that allow you to look up a value in another table based on a criteria, how is this functionality achieved in TSQL?

Thanks

Regards
Melt

I don't think that Meltdown61's question has anything to do with referential integrity. I think the question is whether a CREATE FUNCTION T-SQL statement can be built that mimics MSAccess' DLookup("[colname]","[tblname]","[criteriacol]=" & strCriteria) function.

I am currently attempting to do this, and am having trouble finding success. Here is what I have attempted so far:

CREATE FUNCTION dbo.myDlookUp
(
@strFld varchar(255),
@strTable varchar(100),
@strCrit varchar(255)
)
RETURNS int
AS
BEGIN
declare @lmnop int

SELECT @lmnop = @strFld from @strTable WHERE @strCrit

return (@lmnop)
END

The error I get is Error 137: Must declare the variable '@strTable'. Would anyone have any suggestions or corrections? Many thanks!

Hi Melt

T-SQL has a 'SELECT' statement that will allow you to lookup data ina table, however I suspect that this isn't the answer that you're after.

Please could you provide a little more info on the circumstances under which you are trying to do this so that a best-fit solution can be suggested?

Thanks

Chris

Well, I have partially answered my own question. Dynamic SQL and sp_executesql are two available options I have learned about. However, as explained in detail at http://www.sommarskog.se/dynamic_sql.html, either may not bea good solution, particularly becausethey frequentlyare SQL Injection vulnerabilities. I don't yet have a direct answer to my own question, but I at least wanted to provide the above link so that anyonewho finds these posts, during their research about mimicing DLookup in the SQL Server environment,mightmorecarefully consider these potential solutions.

The reason this function is not in SQL is because there is not a great need for it. You have limited functionality in MS Access and this helps supplement a need. In SQL you can use a subquery,derived table,or an inner join.

Can you explain how you plan on using this function? The way I see it, you should be joining the tables together and not using the function anyway.

• Marked as answer by Kalman TothModerator Sunday, November 27, 2011 6:55 AM

Great answer, thanks!  I think the issue is that you can't use a table name in a variable name. This link gives some alternatives:

http://stackoverflow.com/questions/2838490/table-name-as-variable

which I will also list here:

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT * from yourtable'
EXEC (@sqlCommand)

or

CREATE PROCEDURE [dbo].[GetByName]
    @TableName NVARCHAR(100)
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @sSQL nvarchar(500);

    SELECT @sSQL = N'SELECT * FROM' + QUOTENAME(@TableName);

    EXEC sp_executesql @sSQL
END