By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,031 Members | 1,248 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,031 IT Pros & Developers. It's quick & easy.

Inserting dummy lines and padding

P: n/a
Hi All

Can you please help me with a few queries on adding a header line and
padding rows out.

I apologise profusely for not providing the DDL for this, but I don't have
it. All I have is stored procedure that I'm trying to edit to make the
front-end app display the right data.

The relevant part of the stored procedure that I'm working on is as follow:

Declare StockHelpCursor Scroll Cursor For
Select s.StockID,
ISNULL(sd.ShortDescription, s.StockID) +
space(30-len(ISNULL(sd.ShortDescription,
s.StockID))) +
pl.name +
space(10-len(str(pl.name,10,3))) +
sp.currencyid + str(sp.sellingprice,10,3) +
space(10-len(str(sp.sellingprice,10,3))) +
str(sq.quantityinstock)
From Stock s, StockDescriptions sd, StockQuantities sq,
StockPrices sp, PriceLevels pl
Where (s.StockID Like @theID) And
(ISNULL(sd.ShortDescription, sd.StockID) Like @theName) And
(s.StockID=sd.StockID) And
(s.StockID=sq.StockID) And
(s.StockID=sp.StockID) And
(sp.PriceLevelID=pl.PriceLevelID) And
(sd.LanguageID=@theLanguageID) And
(sp.CurrencyID=@theCurrencyID)
Order By s.StockID
Open StockHelpCursor

PLEASE NOTE: this query works fine apart from the following problems:

1) Creating a header line - I need to insert a header line to this cursor
for the field headers, as the app is stripping off this header. I was
thinking of creating a var, sticking it in-between the Declare and the
Select part and inserting these field headers, but I don't know how to do
this. Any ideas?

2) Padding the results - As you can see from the script, I have tried to pad
out the above fields as the app's output window is basically a textbox, but
they just don't line up.

NOTE: I'm using the 3 in the str(xxx,10,3) bit to make my numbers show at 3
decimal places.

Could you please give me some pointers on how I can pad these out correctly.

Thanks

Robbie


Dec 2 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
This seems to work for the header, assuming you can figure out which is
the first line (minimum StockID) --

CREATE TABLE #Test(N int IDENTITY, String varchar(30))
INSERT #Test SELECT 'Line one'
INSERT #Test SELECT 'Line two'

DECLARE C CURSOR FOR SELECT
Head=CASE WHEN N=1
THEN 'Header'+CHAR(10)+String
ELSE String END FROM #Test
DECLARE @String varchar(30)
OPEN C
WHILE 1=1 BEGIN
FETCH NEXT FROM C INTO @String
IF @@FETCH_STATUS <> 0 BREAK
PRINT @String
END
CLOSE C
DEALLOCATE C

Dec 2 '05 #2

P: n/a
To add a header row (if you mean what I think you mean) you can just do
a SELECT... UNION with your variable. Give it a StockID that will
appear before all of your other StockIDs. For example:

SELECT 0, 'Short Description Name...'
UNION
SELECT s.StockID, ISNULL(sd.ShortDescription, s.StockID) +
.... <rest of your query here>

I'm not sure why this is in a cursor or what you do outside of the
cursor, so I don't know if this will work for you in this case or not.

As far as lining up the output, does the text box used a fixed-width
font? Otherwise, padding with spaces will not help you. You might be
able to do something with tabs, but that might get somewhat
complicated.

-Tom.

Dec 2 '05 #3

P: n/a
Hi Thomas

I think your header thing might just work. Thanks for this

As for the text field, the reason I'm concat-ing the fields is because the
text field currently expects 2 fields and these appear to line up fine. I
just thought by concating the 2nd field with the params and then padding
these out to make nice columns would do it, but my columns go all over the
place.

Am I doing the padding incorrectly?

Rgds Rob
"Thomas R. Hummel" <to********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
To add a header row (if you mean what I think you mean) you can just do
a SELECT... UNION with your variable. Give it a StockID that will
appear before all of your other StockIDs. For example:

SELECT 0, 'Short Description Name...'
UNION
SELECT s.StockID, ISNULL(sd.ShortDescription, s.StockID) +
.... <rest of your query here>

I'm not sure why this is in a cursor or what you do outside of the
cursor, so I don't know if this will work for you in this case or not.

As far as lining up the output, does the text box used a fixed-width
font? Otherwise, padding with spaces will not help you. You might be
able to do something with tabs, but that might get somewhat
complicated.

-Tom.
Dec 2 '05 #4

P: n/a
Astra wrote:
Hi All

Can you please help me with a few queries on adding a header line and
padding rows out.

I apologise profusely for not providing the DDL for this, but I don't have
it. All I have is stored procedure that I'm trying to edit to make the
front-end app display the right data.

The relevant part of the stored procedure that I'm working on is as follow:

Declare StockHelpCursor Scroll Cursor For
Select s.StockID,
ISNULL(sd.ShortDescription, s.StockID) +
space(30-len(ISNULL(sd.ShortDescription,
s.StockID))) +
pl.name +
space(10-len(str(pl.name,10,3))) +
sp.currencyid + str(sp.sellingprice,10,3) +
space(10-len(str(sp.sellingprice,10,3))) +
str(sq.quantityinstock)
From Stock s, StockDescriptions sd, StockQuantities sq,
StockPrices sp, PriceLevels pl
Where (s.StockID Like @theID) And
(ISNULL(sd.ShortDescription, sd.StockID) Like @theName) And
(s.StockID=sd.StockID) And
(s.StockID=sq.StockID) And
(s.StockID=sp.StockID) And
(sp.PriceLevelID=pl.PriceLevelID) And
(sd.LanguageID=@theLanguageID) And
(sp.CurrencyID=@theCurrencyID)
Order By s.StockID
Open StockHelpCursor

PLEASE NOTE: this query works fine apart from the following problems:

1) Creating a header line - I need to insert a header line to this cursor
for the field headers, as the app is stripping off this header. I was
thinking of creating a var, sticking it in-between the Declare and the
Select part and inserting these field headers, but I don't know how to do
this. Any ideas?

2) Padding the results - As you can see from the script, I have tried to pad
out the above fields as the app's output window is basically a textbox, but
they just don't line up.

NOTE: I'm using the 3 in the str(xxx,10,3) bit to make my numbers show at 3
decimal places.

Could you please give me some pointers on how I can pad these out correctly.

Thanks

Robbie


Please do not multi-post! I replied in
microsoft.public.sqlserver.programming

--
David Portas
SQL Server MVP
--

Dec 2 '05 #5

P: n/a
Astra wrote:
As for the text field, the reason I'm concat-ing the fields is because the
text field currently expects 2 fields and these appear to line up fine. I
just thought by concating the 2nd field with the params and then padding
these out to make nice columns would do it, but my columns go all over the
place.

Am I doing the padding incorrectly?


It looks like the padding is correct (although if your column is 30
characters, you might want to pad it out to 31 or 32 so that there is
space when you have a value that fills the full column width.

As I said though, the problem might be in how the front end is
displaying it. Some fonts are fixed-width while others are not. What
this means is that in a fixed-width font the letter "M" is just as wide
as the letter "I" once you take into account space around the letter.
If the font is not fixed-width then a letter "I" may use up 10 pixels,
while an "M" might use up 18 (I have no idea if these numbers are even
in the ballpark, but hopefully you get the idea). As a result, with a
fixed-width font if you have 30 characters on one line and 30 on the
next they will take up the same width no matter what they are. This is
not true if they are not fixed-width.

To see this effect, open up Notepad and type in:

This line is 35 characters long
This one is also 35 characters long

Highlight the two lines and change between Arial and Courier fonts
(under the Format menu). Courier is a fixed-width font and both lines
will be just as long. Arial is not, so the lengths of the lines will
change.

HTH,
-Tom.

Dec 2 '05 #6

P: n/a
The most basic principle of a tiered architecture is that display is
done in the front end and never in the back end. This a more basic
programming principle than just SQL and RDBMS.

You might also ask why Stock , StockDescriptions, and StockQuantities
are all split into their own tables when they are clear attributes of a
stock item. This design flaw is called atrribute splitting for obvious
reasons.

Dec 3 '05 #7

P: n/a
> The most basic principle of a tiered architecture is that display is
done in the front end and never in the back end. This a more basic
programming principle than just SQL and RDBMS.
Wrong! Formatting (display) is done where it is most efficient and scalable
to do it. We aren't using mainframes anymore, it matters how much data is
passed between the server and client/middle tier.

The most basic programming principle is that you look at your architecture
and design for what you have and not implement definitive statements likes
yours willy nilly.

Consider - paging, pivoting etc.... is it really efficient to pass back a
million rows to the client just to get the second page of 50 rows? Nope, it
isn't - but thats what your statement proposes.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com... The most basic principle of a tiered architecture is that display is
done in the front end and never in the back end. This a more basic
programming principle than just SQL and RDBMS.

You might also ask why Stock , StockDescriptions, and StockQuantities
are all split into their own tables when they are clear attributes of a
stock item. This design flaw is called atrribute splitting for obvious
reasons.

Dec 3 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.