How To Execute Dynamic SQL from UDF? 1

[MikeBronner]

My basic question is: how can I execute dynamic SQL (declaring a cursor from dynamic SQL) from within a user-defined function?

Here's the function:

ALTER FUNCTION concat_results
(
	@sql AS VARCHAR(1000)
	,@delimeter AS VARCHAR(255)
	,@pre_text AS VARCHAR(255)
	,@post_text AS VARCHAR(255)
)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @result VARCHAR(8000)
		,@cursor CURSOR
		,@temp VARCHAR(1000)

	SET @sql = 'SET @cursor CURSOR INSENSITIVE FOR ' + @sql
	EXEC @sql

	OPEN @cursor
	FETCH NEXT FROM @cursor INTO @temp
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @result = @result + @delimeter + @temp
		FETCH NEXT FROM @cursor INTO @temp
	END
	SET @result = SUBSTRING(@result, 1, LEN(@result) - LEN(@delimeter))

	RETURN @result
END
GO

And here's the calling code:

SELECT id
	,name
	,dbo.concat_results('SELECT name FROM attributes a, role_attributes ra WHERE a.id = ra.attribute_id AND ra.role_id = ' + CONVERT(VARCHAR(20), id) + ' ORDER BY name', ', ', '', '') AS attributes
FROM roles
ORDER BY name

And here's the error message:

Server: Msg 558, Level 16, State 2, Procedure concat_results, Line 16
Remote function calls are not allowed within a function.

I've read over and over that I can't run dynamic SQL from within a UDF (which was intentional design from MS for security and integrity purposes). My problem with that is that I don't know how to run a stored procedure inside a SQL statement either (i.e. in the calling code) instead of using a user-defined function.

Any ideas?

Take Care,
Mike

 

[gmmastros]

I would suggest that you create a specific user defined function to accomplish this.

Create Function dbo.Concat_Attribute_Names
  (
  @id Integer
  )
Returns VarChar(8000)
As
  Begin

    Declare @temp VarChar(8000)
    Set @temp = ''

    Select @temp = @temp + name + ','
    From   Attributes
           Inner Join role_attributes
             On Attributes.id = Role_Attributes.attribute_id
    Where  role_attributes.role_id = @id
    Order By name

    If Right(@temp, 1) = ',' 
        Select @Temp = Left(@Temp, Len(@Temp)-1)

    Return @Temp
  End

Then call it like this...

SELECT id
    ,name
    ,dbo.Concat_Attribute_Names(id) As Attributes
FROM roles
ORDER BY name

Not tested!

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MikeBronner]

Thanks for you answer. I was afraid of that. :| However, I can't imagine that there isn't a way to convert the results of a query into a (comma) delimited list in the form of a VARCHAR.
Is ther a built-in function that would do this?

Thanks again!
Mike

Take Care,
Mike

 

[gmmastros]

No. Sorry. As far as i know, there is no built in function to do what you want.

At least I have shown you a method for getting a comma delimited list without using a cursor. This method should be a little faster than the cursor method (especially with larger data sets).

In this case, you are getting a different comma delimited list for each record in the roles table, so your only choice (AFAIK) is to use a function. UDF's can execute dynamic sql, so each time you want to do this, you must write a seperate function.

Of course, your other option would be to return each record (without the comma delimited list) and perform the concatenation in your front end.

If there is another way to do this, I'm all ears.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MikeBronner]

Thanks for your help gmmastros!

Your suggestion works (with a few minor tweaks for my database) and is performing the job I need.

As you could tell I was looking for a generic solution I could implement for any SELECT query. Unfortunately this answer is still eluding me (and many others I believe).

Thanks for your resolution!

Take Care,
Mike