dynamic UDF - SQL Server Forums

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

Transact-SQL (2000)

dynamic UDF

Author

Topic

schnoefy
Starting Member

2 Posts

Posted - 2006-10-16 : 10:08:04

Hello,

Unfortunately I've read all those articels after doing a project where an UDF is a main part. Actually as I where testing it I thought it would work. Now I'm filling the UDF with life I have some trouble:
I've tried this:
an UDF should return one specific value from another table for calculation purpose.
like this:
ALTER function dbo.fkt_GetMerkmal2
(
ID as INt
)
RETURNS sql_variant
AS
BEGIN
declare @erg as sql_variant
declare @ssql as nvarchar(1000)
declare @param as nvarchar(500)
set @ssql =N'SELECT @ret=ID2 FROM TBL_TEst WHERE ID1=' + cast(ID as varchar(12))
set @param = N'@ret int OUTPUT'
exec sp_executesql @ssql, @param, @erg OUtput
return convert(varchar(1000),@erg)
END

I just want to get the value to perform some calculations.
I understand that it's not allowed to Do INSERT Or UPDATE actions in an UDF, but why not return an single column value ?
I want to use the UDF in another query like this:

SELECT dbo.fkt_GetMerkmal2(1) + dbo.fkt_GetMerkmal2(5) as TEST

This final query is generated dynamical by my app. I don't insert the value searchs in the UDF in the finale query because it becomes very big and complicated
(there are more columns and tables involved than in this example)

Is there a chance to create such a UDF or is it better to think about a better concept?
can anyone bring me back on track ... befor I get mad..
thanks

Wolfgang

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-16 : 10:39:28

First thing you can't use dynamic sql inside UDF. you can do something like this:

Create function dbo.fkt_GetMerkMal2
(
  @ID int
)
Returns varchar(1000)
as
begin
declare @erg as sql_variant

select @erg = convert(varchar(1000),ID2) FROM TBL_TEst WHERE ID1 = @ID
return (@erg)
end

Harsh Athalye
India.
"Nothing is Impossible"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-16 : 15:19:58

Given that you'll know the type of the column in the table, you don't need to use sql_variant or convert either, just

create function dbo.fkt_GetMerkMal2
(
  @ID int
)
returns varchar(1000)
as
begin
    declare @erg as varchar(1000)

    select @erg = ID2 FROM TBL_TEst WHERE ID1 = @ID
    return (@erg)
end

schnoefy
Starting Member

2 Posts

Posted - 2006-10-17 : 01:36:39

Thank you for your immediate answers

Yes, a user defined function with a fixed statement works fine, like this:

ALTER function dbo.fkt_GetMerkmal2
(
)
RETURNS sql_variant
AS
BEGIN
declare @erg as int
declare @ssql as nvarchar(1000)
declare @param as nvarchar(500)
SELECT @erg=1
return convert(varchar(1000),@erg)
END

by the way, I'm using sql_variant, cause I want to return the content of columns with different types.
As I don't see any way to use the dynamic features in an UDF I will make a distinction of cases in my programm and insert an sub query instead of using an UDF like this:

Doesn't work: SELECT MyUDF(...)+MyUDF(...), A,B,C FROM Tbl_Test

my solution:

SELECT Isnull((SELECT(A FROM Tbl_A WHERE ID=X),0)+Isnull((SELECT(A FROM Tbl_A WHERE ID=Y),0), A,B,C FROM Tbl_Test

happy SQL'ing

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 02:24:25

Why you want to use dynamic sql? you can do it without that.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 02:41:31

Why don't you let your UDF's return ZERO when no value is found?

ALTER FUNCTION dbo.fkt_GetMerkmal2
(
	@ID INT
)
RETURNS VARCHAR(1000)
AS

BEGIN
	DECLARE @erg VARCHAR(1000)

	SELECT	@erg = ID2
	FROM	TBL_TEST
	WHERE	ID1 = @ID

	RETURN ISNULL(@erg, 0)
END

This way, there is no need to do the NULL check later in the SELECT.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources