Scalar function for non-proportional string table

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 2005 Forums

Transact-SQL (2005)

Scalar function for non-proportional string table

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2008-10-16 : 04:50:17

I'm looking for a script or scalar function which solves my problem.

A have a set of data (SELECT Name, Age, Country, Children FROM Persons WHERE Age = 35) or any select statement, and I want to return it as string formed as a table (HTML Table like).

But I don't want to return it as HTML, but as a plain text table. I use a non-proportional font like Courier.

To return it as a HTML table looks something like this:


CREATE FUNCTION [dbo].[fGetSalaryItemAsHTML]
(
	@SalaryId UNIQUEIDENTIFIER
)
RETURNS NVARCHAR (MAX)
AS
BEGIN

	DECLARE @str NVARCHAR(MAX)

	SET @str = '<TABLE>'

	-- Header
	SET @str = @str + '<TR><TD>Type</TD><TD>Bedrag</TD><TD>Netto</TD><TD>Periode</TD><TD>Reden</TD></TR>'

	SELECT @str = @str + '<TR><TD>' + ISNULL (t.Title,'') +  '</TD><TD>'  + ISNULL (CAST (si.Amount AS VARCHAR (10)),'') + '</TD><TD>' + CASE WHEN si.Netto = 1 THEN 'Ja' ELSE 'Nee' END +  '</TD><TD>' + ISNULL (t.Title,'') + '</TD><TD>' + ISNULL (si.Reason,'') + '</TD></TR>'
	FROM tblSalaryItem si
	LEFT JOIN tblChoice t ON t.guid = si.SalaryItemTypeId
	LEFT JOIN tblChoice p ON p.guid = si.PeriodId
	WHERE si.SalaryId = @SalaryId

	SELECT @str = @str + '</TABLE>'


	RETURN @str
END

This is not generic, but it's just an example.

I just want any table to return as a string like this.

Anyway, I doesn't have to be generic as long as I can reproduce this:


____
Name      Age     Country    Children
Henri     21      Holland    1
Michel    23      Belgium    2
Ferry     36      Spain      0
____

Hmm while writing this I think I have to use CLR.

However, I need something like this:

Run through all the records, find out per column which is the longest length. Count all the maximum lenghts and make 1 row this number of spaces and add a line feed and repeat this for the number of rows. Then loop through all the rows and place them at the right coordinates (each column has a start position) and replace the number of spaces with the actual values.

Does my writing make any sense?

Henri
~~~~
There's no place like 127.0.0.1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-16 : 05:05:06

This is really not what SQL is made for...
Do it in your front end.

Sorry
Webfred

Planning replaces chance by mistake

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2008-10-16 : 05:15:00

Hi Webfred,

My "system" does a lot of things besides the user interface like sending e-mails (on certain events). I don't want to write windows services to do that, nor did I ever need that because of CLR. With CLR you can do a lot of these things en personaly I don't mind if a function is T-SQL or CLR. Maybe someone who reads this has invented "this wheel" and comes up with a link. I don't expect someone to write code for me alone.

.

Henri
~~~~
There's no place like 127.0.0.1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources