USP_DATAFORMTEMPLATE_EDITLOAD_MERCHANDISEPRODUCTINSTANCE_2

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@PRODUCTNAME	nvarchar(150)	INOUT
@SALEPRICE	money	INOUT
@BARCODE	nvarchar(23)	INOUT
@PRODUCTINSTANCEOPTIONS	xml	INOUT
@LOOKUPCODE	nvarchar(25)	INOUT
@COST	money	INOUT
@LOOKUPITEMS	xml	INOUT
Definition

Copy

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MERCHANDISEPRODUCTINSTANCE_2(
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @TSLONG bigint = 0 output,
                    @PRODUCTNAME nvarchar(150) = null output,
                    @SALEPRICE money = null output,
                    @BARCODE nvarchar(23) = null output,
                    @PRODUCTINSTANCEOPTIONS xml = null output,
                    @LOOKUPCODE nvarchar(25) = null output,
                    @COST money = null output,
                    @LOOKUPITEMS xml = null output
                )
                as

                set nocount on;

                -- be sure to set these, in case the select returns no rows
                set @DATALOADED = 0
                set @TSLONG = 0

                select
                    @DATALOADED = 1,
                    @TSLONG = MERCHANDISEPRODUCTINSTANCE.TSLONG,
                    @PRODUCTNAME = PRODUCT.NAME,
                    @SALEPRICE = MERCHANDISEPRODUCTINSTANCE.SALEPRICE,
                    @BARCODE = MERCHANDISEPRODUCTINSTANCE.BARCODE,
                    @LOOKUPCODE = MERCHANDISEPRODUCTINSTANCE.LOOKUPCODE,
                    @COST = MERCHANDISEPRODUCTINSTANCEVENDOR.COST,
                    @LOOKUPITEMS = dbo.UFN_MERCHANDISEPRODUCTINSTANCE_GETLOOKUPITEMS_TOITEMLISTXML(@ID)
                from dbo.MERCHANDISEPRODUCTINSTANCE
                inner join dbo.PRODUCT on
                    MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID = PRODUCT.ID
                    inner join dbo.MERCHANDISEPRODUCTINSTANCEVENDOR on
                    MERCHANDISEPRODUCTINSTANCEVENDOR.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEPRODUCTINSTANCE.ID
                where MERCHANDISEPRODUCTINSTANCE.ID = @ID

                set @PRODUCTINSTANCEOPTIONS = (
                    select 
                        OPTIONID as '@OPTIONID',
                        OPTIONNAME as '@OPTIONNAME',
                        OPTIONSEQUENCE as '@OPTIONSEQUENCE',
                        OPTIONVALUEID as '@OPTIONVALUEID'
                    from dbo.UFN_MERCHANDISE_GETPRODUCTINSTANCEOPTIONS_2(@ID)
                    order by OPTIONSEQUENCE
                    for xml path ('ITEM'), type, elements, root('PRODUCTINSTANCEOPTIONS'), BINARY BASE64
                )

                return 0;