SQL Query Help

microsoft.public.sqlserver.programming

2 Dec 2005 1:10 PM

Astra

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