"Astra" <No@Spam.com> wrote in message
news:%23OFsYA29FHA.3804@TK2MSFTNGP14.phx.gbl...
> 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

> 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