formatting output via single select

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

SQL Server Development (2000)

formatting output via single select

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-18 : 08:27:24

Jason writes "I am using sql server 2000 standard edition. SP 1 ( I'm not the server's DBA)

I have a query that I have written that formats each of my output columns individually. If the column is a number of any kind, I am to right justify the number and put zero's before it to sure each column takes up its specified length. If the column is a varchar / char, I must left justify it and pad it on the right with spaces to ensure that column's length is met. There are about 60 to 80 columns, each with varying lengths.

The code I am using for the formatting above is like the following:

For text:
RTRIM(EF.PatientMedRecNum) +
REPLICATE(' ', @PatientMedRecNumLen - LEN( EF.PatientMedRecNum))

For numbers:
REPLICATE('0', @PatientSchoolLen - LEN( EF.PatientSchool)) + RTRIM( EF.PatientSchool) +

The way I am currently doing this (God save me from what I am about to say) is declaring a variable for all of my columns and then declaring a length variable for all of my columns. I do this so I can use a cursor. I know they are bad, but T-SQL being set oriented makes doing this kind of formatting on each individual record quite difficult.

I must also output each record to a file (the same file ). This much I have accomplished via the xp_cmdshell and dos commands.

I have tried creating a DTS package to do this, but I can't get it to work. I have a revised version of the qry that hard codes the lengths. For space purposes I can not list it here but the gist of it is the following:

SELECT @ReturnValue = RTRIM(EF.PatientMedRecNum) + REPLICATE(' ', 25 - LEN( EF.PatientMedRecNum)) +
LTRIM(RTRIM( EF.PatientFirstName)) + REPLICATE(' ', 25 - LEN( EF.PatientFirstName))
FROM EnrollmentForm as EF

Pardon how it looks on the web page. I really do have it pretty in query analyzer. The problem with this is that I can't write it to a file. A varchar can hold a max of 8000 characters. If each record will be 2200 chars before everything is all said and done, so I can't just keep adding each result to the same variable. Even if there were a variable big enough ( and I know there is) it sounds like sooner or later that is destined to break.

I needed a loop. It had to format each field, copy a concatenation of all fields to a variable and then print to a file. A cursor is the only way I know how to do this. From other aritcles I have read here, a while loop's performance seems to be even worse than a cursors, but I have not yet tried using a while loop.

I have quite literally struggled with this monster for about two weeks. Yes I have a working version that meets all business requirements, but is 1200 lines of code and will be so difficult to maintain I would have to write a program to parse throught the .sql file to make new additions to the qry. Any help what so ever would be appreciated."

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-18 : 08:55:20

To right justify
right(space(25) + fld,25)
To left justify
left(fld + space(25),25)

Have a look at
http://www.nigelrivett.net/sp_CreateDataLoadScript.html
It generates a data load script but accesses column definitions to do it, (You don't need cursor but that's probably not a bad thing as performance probably isn't an issue).

For your varchar size problem.
You can build a select statement in a text column of a temp table then put this into several variables to exec it.
The output data you can also put into a text column and use bcp to output.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-18 : 18:41:36

And fire the DBA. He sucks!

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-18 : 18:48:11

EEEEEEEK
sql server 2000 standard edition. SP 1

Put sp3a on or at least sp2 with the Slammer patch MS02-061 .

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources