SQL Server Performance Forum – Threads Archive

Function to give Comma Delimited output

Heya,
I have been asked to come up with a function that will accept an SQL string as input, and return the results in a comma delimited string. The input string will only select one column…I came up with the following procedure:
CREATE PROCEDURE [dbo].[Run_CommaDelimited] @SQLString nvarchar(4000) AS DECLARE [email protected] as nvarchar(4000),
@StringToExecute as nvarchar (4000),
@SelectColumn as nvarchar(500),
@FromWhere as nvarchar(4000),
@Output as nvarchar(4000) SET @SelectColumn = RIGHT((LEFT(@SQLString, PATINDEX(‘% FROM %’, @SQLString))), PATINDEX(‘% FROM %’, @SQLString)-6) SET @FromWhere = RIGHT(@SQLString, len(@SQLString) – PATINDEX(‘% FROM %’, @SQLString)) SET @StringToExecute = ‘DECLARE @ResultList as nvarchar (4000) SELECT @ResultList = (COALESCE(@ResultList + ”, ”, ””) + ‘ + @SelectColumn + ‘)’ + @FromWhere + ‘;’ + ‘SELECT @ResultList’ EXEC (@StringToExecute)
GO
I have since been told that my solution needs to be able to be run from a select statement, so the procedure is not an option (unless someone knows how to run a procedure using a select statement???) Any ideas on making this into a Function? Or does anyone know an easier way to return results as a comma delimited string??? Cheers,
Ben

Hi Ben, not wanting to be rude, but on the face of it this seems like a weird idea… You can’t make it into a function since functions can’t execute dynamic sql however the user calling this will need rights on the table and it would be a hackers dream… I’d seriously consider other ways of doing this… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

DTS is not an option?

Twan – if I may intrude here for a moment – This is exactly the situation where you CAN disarm the hackers: since all you need is the result of the SELECT, you can wrap the code in BEGIN TRANSACTION and ROLLBACK TRANSACTION – any action query that gets passed into the function is rolled back, but the SELECT part is executed normally. More generally speaking … we create non-generic functions for this, only using a numeric foreign key value as criteria. Now if only we could return a TEXT value from a UDF … (Yes, I know about creating a TABLE variable, but then you will still run into looping issues.)

yes but you can’t rollback things like exec xp_cmdshell( ‘del *.*’ ) and hundreds of other examples… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> also performance would be worse since a) the app has to pass the whole statement rather than a proc call with params b) sql needs to compile the statement in my humble opinion dynamic sql tends to be used as a convenience thing rather than any solid/real reasons… I stay away from any dynamic sql as much as possible, just like not allowing any direct DML on any database… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

Twan, Thanks for clearing that up. As long as you can define the function with true numeric parameters only, then the problem does not exist, right? We compile this kind of comma-delimited lists with one UDF for each specific subtable, based on the foreign key referring to the IDENTITY column on the parent table. For reports, SQL will do a much better job doing this than any client app would do, and as long as you limit the number of records in the main query … SQL Server does seem to have some kind of shortcuts for evaluating these UDFs.

Hi ya, yeah numeric parameters are ok, although again I’d tend to stay away from dynamic sql if at all poss <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

Thankyou all for your comments! Yes, I agree it does seem like a slightly unusual idea, but it was the task i was set! The reason they want they funtion as i understand it, is so that it is very flexible and can be used for ad-hoc reporting on any table/data. I dont think DTS is an option- can you execute a DTS package from a select statement? Adriaan, you mentioned you compile these kind of lists with a UDF for each table- is that done using COALESCE or is there another way? Can you pass a table variable as an input parameter?? Thanks,
Ben

Ben, You can return a TABLE variable from a UDF – look it up in BOL, the call must be made using a special syntax. It would be a one-row table with just a TEXT field. (Obviously you can pass a TABLE variable as a parameter between SPs and UDFs.) Given the nature of our data, we don’t often run into problems with the 8,000 character limit – plus the syntax is so much easier when you use the variable … CREATE FUNCTION dbo.MyList (MyParam INT)
RETURNS @Str VARCHAR(8000)
AS
BEGIN SET @Str = ” SELECT @Str = @Str + CASE WHEN LEN(@Str) > 0 THEN ‘,’ ELSE ” END + T.MyField
FROM dbo.MyTbl AS T WHERE T.FKField = @MyParam
ORDER BY T.MyField RETURN @Str END
We’ve found COALESCE is slow compared to CASE, so we use CASE all the time.

Thanks for your help <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> so much for me to learn!!!

A more robust way might be to have the UDF return a table of multiple rows, each containing a single varchar column, with the fields appended. I guess it depends where you intend to call the UDF from. Why a UDF, and not a stored proc? I realise UDF is the requirement, but is there a good reason or just because youve been told? Also in relation to hacking, its not just data modification we want to avoid. Most hacks will utilise a few (if not many) different techniques to get what they want. For example, they might use your routine to dump all your sensitive user details to a known location on disk. Not much use to them on its own, but if they were then to use an unrelated IIS exploit, they may be able to download that file. Just my 2 cents <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Hey Chappy,
I have been asked for a UDF as they want to be able to get the results using a Select statement…apart from that, it is just cause i have been told!

Hi ya if you have to use a UDF, then your only options (I think) are:
– get the UDF to return a table variable with just a single varchar column (add an int column if you need the results to come back in a predictable order)
– you’ll need a UDF per table, each with their own parameters
– you can’t use dynamic sql in a UDF I know that you’ve been tasked this, but in all honesty (and in my opinion) it is not a practical or smart solution… I think that it will only lead to hardship later on if the UDF is to be used in a select statement, then how do they see it being joined to anything? and if it isn’t joined to anything then why would you use a UDF over a proc? sorry to question the motives, but in some cases giving the answer to the problem is not the same as offering a viable solution… Cheers
Twan

Thanks Twan,
I guess as i learn more, i will be more able to reconise when to question the tasks i am set!

It is never too late to question why <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> that’s pretty much my motto… Cheers Twan

]]>