USP_REPORT_MERCHANDISEPRODUCTINSTANCELABEL

create procedure dbo.USP_REPORT_MERCHANDISEPRODUCTINSTANCELABEL
(
    @PRINTSESSIONID uniqueidentifier
)
as

declare @MAXNUM integer
declare @CURNUM integer = 0
declare @CURRENCYSYMBOL nvarchar

select top 1 @CURRENCYSYMBOL = CURRENCYSYMBOL from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1


select @MAXNUM = MAX(QUANTITY) from MERCHANDISEPRODUCTLABLEPRINTHISTORY where PRINTSESSIONID = @PRINTSESSIONID

declare @NUMBERTABLE table (NUMBER int)

while @CURNUM < @MAXNUM
    begin
        set @CURNUM = @CURNUM + 1
        insert into @NUMBERTABLE (NUMBER) values (@CURNUM)    
        continue
    end

select
    MERCHANDISEPRODUCTLABLEPRINTHISTORY.ITEMDETAILS,
    MERCHANDISEPRODUCTLABLEPRINTHISTORY.BARCODE,
    @CURRENCYSYMBOL + cast(MERCHANDISEPRODUCTLABLEPRINTHISTORY.SALEPRICE as nvarchar) SALEPRICE
from
    dbo.MERCHANDISEPRODUCTLABLEPRINTHISTORY inner join @NUMBERTABLE NUMBERTABLE on
        MERCHANDISEPRODUCTLABLEPRINTHISTORY.QUANTITY >= NUMBERTABLE.NUMBER
where
    PRINTSESSIONID = @PRINTSESSIONID
order by
    MERCHANDISEPRODUCTLABLEPRINTHISTORY.QUANTITY desc, MERCHANDISEPRODUCTLABLEPRINTHISTORY.MERCHANDISEPRODUCTID, MERCHANDISEPRODUCTLABLEPRINTHISTORY.MERCHANDISEPRODUCTINSTANCEID