Is This SQL Stored Prodcedure Is Valid

what i want to achive is the proc sh'd return a master-detail value in one go.


master value should be returned with Out Parameter and detail value as a recordset.





will it return the recordset of detail table as below....





Create Procedure ProductDetail


(


@ProductID int,


@ProductCode varchar(15) OUTPUT,


@ProductName varchar(60) OUTPUT,


@CategoryID int OUTPUT,


@CategoryName varchar(60) OUTPUT,


@Image1 varchar(256) OUTPUT,


@Image2 varchar(256) OUTPUT,


@UnitPrice smallmoney OUTPUT,


@UOMValue numeric(9) OUTPUT,


@UOMName varchar(10) OUTPUT,


@ShippingWeight numeric(9) OUTPUT,


@Directions varchar(1500) OUTPUT,


@Ingrediants varchar(1500) OUTPUT,


@Warnings varchar(1500) OUTPUT,


@ShortDescription varchar(1000) OUTPUT,


@LongDescription varchar(2000) OUTPUT,


@NutritionFacts varchar(1000) OUTPUT,


@SearchKeywords varchar(500) OUTPUT,


@IsTaxable varchar(15) OUTPUT,


@CreatedBy varchar(60) OUTPUT,


@CreatedOn varchar(15) OUTPUT,


@UpdatedBy varchar(60) OUTPUT,


@UpdatedOn varchar(15) OUTPUT,


@Status int OUTPUT


)


AS





SELECT


@ProductCode = ProductCode,


@ProductName = ProductName,


@CategoryID = CategoryID,


@CategoryName = (select CategoryName from mCategory where CategoryID=a.CategoryID),


@Image1 = isnull(Image1,''),


@Image2 = isnull(Image1,''),


@UnitPrice = isnull(UnitPrice,0),


@UOMValue = isnull(UOMValue,0),


@UOMName = isnull(UOMName,''),


@ShippingWeight = isnull(ShippingWeight,0),


@Directions = isnull(Directions,''),


@Ingrediants = isnull(Ingrediants,''),


@Warnings = isnull(Warnings,''),


@ShortDescription = isnull(ShortDesc,''),


@LongDescription = isnull(LongDesc,''),


@NutritionFacts = isnull(NutritionFacts,''),


@SearchKeywords = isnull(SearchKeywords,''),


@IsTaxable = case when isnull(IsTaxable,0)=0 then 'No' else 'Yes' End,


@CreatedBy = isnull((select LName + ',' + FName from mUser where UserID=InsertedBy),''),


@CreatedOn = InsertedOn,


@UpdatedBy = isnull((select LName + ',' + FName from mUser where UserID=UpdatedBy),''),


@UpdatedOn = UpdatedOn,


@Status = Convert(int,isnull(Status,0))


FROM


mProduct a


WHERE


ProductID = @ProductID








SELECT


ID as PricingDetailID,


isnull(PricingFromQnty,0) as PricingFromQnty,


isnull(PricingToQnty,0) as PricingToQnty,


isnull(RangePrice,0) as RangePrice,


Convert(int,isnull(Status,0))as Status


FROM


dProduct


WHERE


ProductID = @CategoryID








GO


SET QUOTED_IDENTIFIER OFF


GO


SET ANSI_NULLS ON


GO





Regards,


Bhairav