I would like to create a function that.....

[LenardD]

I would like to create a function that retrieves the value of a field by passing the string name of the table and field. For example:

returnValue = dbo.LookupValue('BusinessDetails', 'Telephone')

I could do it via dynamic sql but is there any other way?

Thanks.

 

[zhavic]

CREATE FUNCTION LookupValue( @cTable_name varchar(100), @cColumn_name varchar(100) )
RETURNS varchar(100)
AS
BEGIN
DECLARE @cReaturn_value varchar(100)

IF @cTable_name = 'BusinessDetails' AND @cColumn_name = 'Telephone'
SELECT @cReaturn_value = Telephone FROM BusinessDetails
ELSE
IF @cTable_name = 'Some_other_table' AND @cColumn_name = 'Some_Column'
SELECT @cReaturn_value = Some_Column FROM Some_other_table
ELSE
... other cases

RETURN @cReaturn_value
END

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[hmckillop]

Here is a stored proc which will return the values you request. You could declare a table variable to hold the return values if you require this call within a stored proc.

create Procedure ufn_aaa
(
@pi_Fieldname varchar(20),
@pi_tablename varchar(20)
)

AS
BEGIN

DECLARE @SQLString NVARCHAR(500)

SET @SQLString = 'SELECT ' + @pi_fieldname + ' FROM ' + @pi_tablename
EXECUTE dbo.sp_executesql @SQLString
END

 

[LogicalmanUS]

LenardD

Both of the above suggestions will work, but the former is not expandable automatically, in that it relies on knowledge of the table and column names for each select statement.
hmckillop's answer is exactly what you need in respect of any table and any column name, and I have starred it, appropriately.
The only thing I would point out is that there is no fault tolorance built in, in that if the table or column does not exist then it will return an error.
You could expand this sp to include a check against sysobjects table in the given db, and ensure the table exists, and then check the syscolumns table for the given column, based on the object id of the table.

If you need that code, just ask ...

Logicalman

 

[LenardD]

Thanks Guys,

The first method is what I wanted to avoid because I would have to hardcode the columns and tables. I'll actually be looking them up from a table to build a custom list of preferences for the user to pick from.

The second method is using dynamic sql which as you said is more flexible. I thought there would be another way so I didn't have to use dynamic sql......by using something like

COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME), COLUMN_NAME, @fieldname

By the way, I forgot to pass the ID of the record to lookup. It should have been:

returnValue = dbo.LookupValue ( @recID, @tableName, @fieldName )

--Thanks

 

[LogicalmanUS]

LenardD,

I understand what you are trying to find, but I cannot see how this is easily achieveable without DSQL.
The use of COLUMNPROPERTY will only return the properties of the column, rather than data from that column. In other words, this is a schema function only.

Logicalman

 

[LenardD]

Thanks LogicalManUS,

I guess DSQL is the way to approach it. I just wanted to avoid giving SELECT permissions to those tables I would be using.

 

[LogicalmanUS]

LenardD,

I have the same problem, on almost a daily basis, concerning providing access to logins. I therefore make a point of using column-level permissions, rather than at table level, where possible.

Logicalman

 

[LenardD]

hmckillop,

How would you modify your function so that it returns a string....for example something that is functionally the same as:

SELECT @telephone = Telephone FROM BusinessDetails WHERE ID = @ID

RETURN @telephone

 

[hmckillop]

Depends on whether you ID column is always called ID, I will assume that it isnt. (if it is it becomes easier by removing the additional parameter)

CREATE Procedure ufn_aaa
(
@pi_Fieldname varchar(50),
@pi_tablename varchar(50),
@pi_IDfieldname varchar(50),
@pi_ID INT
)

AS
BEGIN

DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)


SET @SQLString = 'SELECT ' + @pi_fieldname + ' FROM ' + @pi_tablename + ' WHERE ' + @pi_IDFieldName + ' = @MyID'
SET @ParmDefinition = '@MyID int'

EXECUTE dbo.sp_executesql @SQLString, @ParmDefinition,
@MyID = @pi_ID
END


You dont necessarily need to use parameters, I within the sp_executesql call, you can simply do it by

SET @SQLString = 'SELECT ' + @pi_fieldname + ' FROM ' + @pi_tablename + ' WHERE ' + @pi_IDFieldName + ' = ' + cast(@pi_ID as varchar(10))

EXECUTE dbo.sp_executesql @SQLString

 

[LenardD]

I just realized that your code is for a procedure and not a function. I wanted a function so that I can use it in my SELECT.

I tried to convert it but when I use it, something about "Only functions and extended stored procedures can be executed from within a function."

Here is what I have:


[pre]
CREATE FUNCTION dbo.LookupValue
(
@key int,
@sql nvarchar(100)
)
RETURNS nvarchar(1024) AS
BEGIN

DECLARE @val nvarchar(1024)

IF( @sql != null)
EXECUTE dbo.sp_executesql @sql, N'@key int, @val nvarchar(1024) OUTPUT', @key = @key, @val = @val OUTPUT
ELSE
SET @val = ''

RETURN( @val )

END
[/pre]


You will note for this function, I've decided to pass the SQL statement instead of the table and field. So the "@sql" variable would hold something like:

"SELECT @val = Telephone FROM BusinessDetails WHERE ID = @key"

--Lenard