dynamic sql in UDF

20 Jul 2006 9:33 AM

ivan

Hello!

I need to use dynamic sql in UDF. There are a lot of function in the
database like:

ALTER FUNCTION ...... (@Date1 int, @Date2 int)
RETURNS @Result TABLE (a int, b int)

AS
BEGIN
    INSERT INTO @Result
    SELECT columnX, columnY
    FROM TableZ

    RETURN
END

There are more details in these functions but all functions are
similary. Only diferents are columnX, columnY and TableZ. It has to be
a function (because of another things), and, I saw only one solution. I
can put columnX, columnY and TableZ in parameter list but then I need
create and execute dynamic SQL in function.
I need function like:

FUNCTION ...... (@Date1 int, @Date2 int, @X nvarchar (255), @Y
nvarchar (255), @Z nvarchar (255))
RETURNS @Result TABLE (a int, b int)

AS
BEGIN
    exec 'INSERT INTO @Result
            SELECT '+@X+','+@Y+'
            FROM '+@Z

    RETURN
END

I saw that it is not possible but ... I don't know what I should to do?
I am wondering if somebody has an idea how to solve this problem.

Regards,
Ivan

20 Jul 2006 11:43 AM

ML

Dynamic SQL (i.e. the EXEC statement) is not allowed in UDFs. I'd suggest
creating as many UDFs as needed.

Another option - a kludge if you ask me - would be to put as many IF
statements into a single UDF as there are tables you need to select from.

What exactly are you traing to acomplish?

ML

---
http://milambda.blogspot.com/

20 Jul 2006 12:03 PM

ivan

I try to avoid solutions that you suggest me. In the database already
exist about 50 similar UDFs (your first suggest) and I try to reduce
number of those functions. Your second solution is similar: a lot of
UDF or a lot of IF statements ...
So, I try to make one function which can do instead of 50 similar
functions BUT it must be function (not stored procedure).

Ivan

ML wrote:
Show quote

> Dynamic SQL (i.e. the EXEC statement) is not allowed in UDFs. I'd suggest
> creating as many UDFs as needed.
>
> Another option - a kludge if you ask me - would be to put as many IF
> statements into a single UDF as there are tables you need to select from.
>
> What exactly are you traing to acomplish?
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

20 Jul 2006 12:15 PM

ML

Why?

What's wrong with many UDFs if you need them? In other words: what's wrong
with several pre-compiled objects that's not wrong with one kludgy object?

ML

---
http://milambda.blogspot.com/

20 Jul 2006 12:38 PM

ivan

OK. I see that I have not other solution except many UDFs and I will
continue with this solution. But, what is wrong... For example: I have
50 UDFs and next statements in them:

In the first:
   SELECT a1, b1
   FROM c1
   WHERE group = 'programming'
In the second:
   SELECT a2, b2
   FROM c2
   WHERE group = 'programming'
.... and so on ... 50 times in 50 UDF (maybe 100 UDFs later)

If I want to add something in functions (for example: WHERE
group='programming' and topic = 'dynamic sql in UDF') or if I want to
change anything, I must change this in 50 functions.

In any way, thanks for your help.

Regards,
Ivan

ML wrote:
Show quote

> Why?
>
> What's wrong with many UDFs if you need them? In other words: what's wrong
> with several pre-compiled objects that's not wrong with one kludgy object?
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

20 Jul 2006 1:06 PM

daw

> If I want to add something in functions (for example: WHERE
> group='programming' and topic = 'dynamic sql in UDF') or if I want to
> change anything, I must change this in 50 functions.

For this case you can write script (using dynamic sql) to generate respective
"alter function" statements...

20 Jul 2006 1:11 PM

ML

If I understand correctly, these functions are referring to tables that are
structurally similar or even equal? Is your model properly normalised?

Anyway, you could design a script or even a procedure to alter the functions
for you. At least one part of the job can then be automated.

ML

---
http://milambda.blogspot.com/

20 Jul 2006 3:24 PM

Steve Kass

It sounds like you have many tables with similar structure, but they have
different names and the columns have different names. A better solution is
probably a redesign of your database. If you have one table:

create table AllTables (
origTableName varchar(10) not null,
colA int,
colB int
)

your function could be

create function f(
@tableName sysname
)
....
insert into @result
select columnX, columnY
from AllTables
where origTableName = @tableName

You could also create this "supertable" as a view:

create view AllTables as
select 'TableZ', columnX, columnY from TableZ
union all
select 'Table2', c1, c2 from Table2
union all
....

Steve Kass
Drew University

Show quote

"ivan" <ivanmilo***@gmail.com> wrote in message
news:1153388031.112276.83880@i42g2000cwa.googlegroups.com...
> Hello!
>
> I need to use dynamic sql in UDF. There are a lot of function in the
> database like:
>
> ALTER FUNCTION ...... (@Date1 int, @Date2 int)
> RETURNS @Result TABLE (a int, b int)
>
> AS
> BEGIN
> INSERT INTO @Result
> SELECT columnX, columnY
> FROM TableZ
>
> RETURN
> END
>
> There are more details in these functions but all functions are
> similary. Only diferents are columnX, columnY and TableZ. It has to be
> a function (because of another things), and, I saw only one solution. I
> can put columnX, columnY and TableZ in parameter list but then I need
> create and execute dynamic SQL in function.
> I need function like:
>
> FUNCTION ...... (@Date1 int, @Date2 int, @X nvarchar (255), @Y
> nvarchar (255), @Z nvarchar (255))
> RETURNS @Result TABLE (a int, b int)
>
> AS
> BEGIN
> exec 'INSERT INTO @Result
> SELECT '+@X+','+@Y+'
> FROM '+@Z
>
> RETURN
> END
>
> I saw that it is not possible but ... I don't know what I should to do?
> I am wondering if somebody has an idea how to solve this problem.
>
> Regards,
> Ivan
>