I would like to create a function that......
-
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.
-
Hi,
quote:
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 don't think this could be reasonable handled without dynamic sql
Although you might write a function that evaluates the table name parameter in a case structure, this would certainly become messy when evaluating for every possible field name within one function.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You would have to pass the primarykey as well, right?
You could create one massive lookup table that consisted of tablename, column, primarykey, value. Create triggers on all tables to maintain it. Then you could do it without dynamic sql or ugly case statements.
I'd recommend just retrieving the entire record, if you end up needing any other column you'll have it and you save the round trip, plus a couple reads.
Hide this functionality in an object somewhere and forget about it. It doesnt cost enough to worry about and in my opinion you're trying to build the wrong pattern.
Andy
-
Yes, I would be passing an ID as well. I forgot to show it.
Regarding the lookup table....I want to avoid that. I guess dynamic sql would be the cleanest. I wanted to avoid giving SELECT permissions to those tables. I though one could do it using statements like COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME) , COLUMN_NAME, @columnName in the SQL statment.
What I'm trying to create is a custom list that I will use to bind a dropdown to. I store the mask in a table which I use to create the selection list. For example:
Mask 1: Telephone: {BusinessDetails.Telephone}
Mask 2: Fax: {BusinessDetails.Fax}
I parse the mask and replace it with the actually value.
Telephone: 403-555-1212
Fax: 403-555-1212
Thanks for your post.
-
Andy,
How would you code something like this in Dynamic SQL? I basically want to grab the value of the field and return it.
SELECT @telephone = Telephone FROM BusinessDetails WHERE ID = @ID
RETURN @telephone
-
Hi lenardd,
quote:
How would you code something like this in Dynamic SQL? I basically want to grab the value of the field and return it.SELECT @telephone = Telephone FROM BusinessDetails WHERE ID = @ID
RETURN @telephone
From scratch I would say
DECLARE @ID varchar(10),
DECLARE @stmt NVARCHAR(255),
SET @ID = somevalue
SET @stmt = 'SELECT Telephone FROM BusinessDetails WHERE ID ='+@ID
EXEC sp_executeSQL (@stmt)
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank that won't work in a function. Don't you have to explicitly return a variable? Here is what I've come up with so far but am having some problems making it work:
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 )
ENDThe @sql variable would contain something like:
SELECT @val = Telephone FROM BusinessDetails WHERE ID = @key
I get some error saying it can't be run in a function.
Does anything jump out at you?
-
First of all, you cannot use a variable to return from a dynamic SQL.
Something like
EXEC 'SELECT @val=Field FROM TABLE'
does not work.
You can use a cursor to get the value though, by returning a recordset and assigning it to a cursor.
Another solution would be to use a temporary table to hold the value.
-
aargh, missed the point with the function.Sorry about that!
Like NPeeters said it won't work. After executing your statement your variable is out of scope. So you have to workaround that. Noel mentioned a temp table, which you could query in a second statement. I guess I would try this!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Actually, lenardd is on the right track with the OUTPUT handling in the sp_executesql. I have been able to use variables nicely with dynamic SQL.
The greater challenge in my mind is that the function needs to remember that many different data types may be needed. The SQL and related OUTPUT variables would need to be specific to each datatype used. Not difficult, but important.
Some questions -
1) If you are binding a dropdown, aren't you really look for a set of multiple results?
2) Do you have the flexibility to have the intelligence in the client? Datasets often make this type of work fairly straightforward.
Guarddata-
-
Frank you can use OUTPUT parameters.......have a look:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q262499
GuardData, the table that I'm binding actually has a list of masks and "@sql" which I select. But before I return that list, I use the dbo.LookupValue function to transform (normalize) the mask so the list is customized to a user. For example:
Mask 1: Telephone: {@sql}
Mask 2: Fax: {@sql}
Mask 3: Mobile {@sql}
The above gets normalized using the lookup function to:
Telephone: 403-555-1212
Fax: 403-555-1212
Mobile: 403-555-1212
This is the lists that is returned for binding.
In my original question, I was going to pass the Table, Field, ID to the lookup function. As you can see I changed my mind and just pass a SQL statement to do the lookup.....its simplier. However, the error I'm getting says:
"Only functions and extended stored procedures can be executed from within a function."
Does that mean I can't use EXEC and sp_executesql in a function?
--Lenard
-
That is correct - I ran into that problem also a while back (no sp_executesql inside a function). Kind of an obstacle.
Perhaps you can build the SQL like
'SELECT ' + @theField + ': + CONVERT( VARCHAR(50), ' + @theField + ' ) FROM ' + @theTable + ' WHERE ' + @theCondition
Good luck with it.
Guarddata-
-
GuardData,
How do I do the FUNCTION RETURN after the SELECT is executed?
--Lenard
-
Sorry Lenardd - I left out that part of the response
. I was thinking you could use this in place of the function. Is there any chance of using a stored procedure instead?Guarddata-
-
DataGuard,
I wanted to do the "normalization" in the SELECT statement (thus the reason for a function). From your post, it sounds like that won't be possible and as far as I know, one can't use a SP in the SELECT statement. Looks like I will have to rethink my process. Darn. 🙂
Thanks for your replies.
--Lenard
aargh, missed the point with the function.
. I was thinking you could use this in place of the function. Is there any chance of using a stored procedure instead?Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply