how can i execute SQL in 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)

how can i execute SQL in UDF

Author

Topic

vinod_mnr
Starting Member

1 Post

Posted - 2005-03-17 : 03:00:45

Hi,

i have written a udf in sql server 2000 which will return a sql statement e.g.
"SELECT EMP_NAME FROM EMP_MASTER"
I would like to execute this sql in the same function and would like to use the resultset of this sql statement. how can i get resultset of this sql.

e.g.
i have a function like

CREATE function fncGetLogDrildown(@stablename varchar(255), @scolname varchar(500), @spkcolname varchar(255), @lrecid smallint)
returns varchar(2555)
as
BEGIN

DECLARE
@sSql nvarchar(2500)

SELECT @sSql = 'SELECT ' + convert(varchar(255), @scolname) + ' FROM ' + convert(varchar(500),@stablename) + ' WHERE ' + convert(varchar(255), @spkcolname) + '=' + convert(varchar(50),@lrecid)

return @sSql
END

Now, this function will return me a sql e.g. "SELECT EMP_NAME FROM EMP_MASTER" BUT i require the resultset not a sql statement as string.

Thanks in advance ,
vinod

gpl
Posting Yak Master

195 Posts

Posted - 2005-03-17 : 05:19:30

Exec is not allowed within a UDF
This is because it could return different results everytime it is called and that is NOT ALLOWED
This is why GetDate and random numbers are also not allowed
Graham

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-17 : 05:42:35

use a stored procedure for this.

gpl: random numbers and get date can be allowed in a UDF with a little trick.
you create a view that calls select getdate() as col1 -- or rand()
and you do
select col1 from viewName
in your UDF.

Go with the flow & have fun! Else fight the flow

gpl
Posting Yak Master

195 Posts

Posted - 2005-03-17 : 12:58:21

spirit1
now that is sneaky !!
I like it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources