Tuesday, September 11, 2012

Passing Subqueries to Stored procedure


ALTER PROC [dbo].[sp_GetPinnedSalesDetail]
    -- Add the parameters for the stored procedure here
    @orderNumber varchar(50) = 'VPSORD8013',
 @merchantAccount varchar(50),
    @userlogin  varchar(50)
AS
BEGIN
DECLARE @ReprintCount int
DECLARE @UserName varchar(50)

    SET NOCOUNT ON;
SET @ReprintCount=(SELECT COUNT(*) AS Expr1 FROM  WebAdminDBTest.dbo.AuditTable WHERE
 (WebAdminDBTest.dbo.AuditTable.masterAccount = @merchantAccount) AND (WebAdminDBTest.dbo.AuditTable.userName = @userlogin)
AND WebAdminDBTest.dbo.AuditTable.description = @orderNumber)


SET @UserName=(SELECT WebAdminDBTest.dbo.WebUserSales.subuser_name FROM  WebAdminDBTest.dbo.WebUserSales WHERE (WebAdminDBTest.dbo.WebUserSales.voucher_orderno = @orderNumber))


SELECT     @UserName AS username,@ReprintCount AS reprintcount,dbo.TSSMerchant.MerchantAccount, dbo.TSSMerchant.CompanyName,
dbo.TMPVoucherOrder.OrderNo,
 dbo.TMPVendor.CompanyName AS Vendor,
 TMPVendor.Description1 AS Instruction1, TMPVendor.Description2 AS Instruction2,
TMPVendor.Description3 AS Instruction3, TMPVendor.Description4 AS Instruction4, TMPVoucher.ExpiryDate,
dbo.TMPVoucherType.Description as VoucherType,
                      dbo.TMPVoucherType.VoucherCode, dbo.TMPVoucherOrderDetail.TotalSellingPrice,TMPVoucherType.FaceValuePrice,TMPVoucherType.AirtimeWindow, dbo.TMPVoucherOrderDetail.Quantity, dbo.TMPVoucher.S1,
                      dbo.TMPVoucher.S2, dbo.TMPVoucher.S3,dbo.TMPVoucher.RechargePin, dbo.TMPServerOrder.ImportDate, dbo.TMPVoucherOrder.OrderDate
FROM         dbo.TMPVoucherType INNER JOIN
                      dbo.TMPVoucher ON dbo.TMPVoucherType.TMPVoucherTypeID = dbo.TMPVoucher.TMPVoucherTypeID INNER JOIN
                      dbo.TMPVoucherOrder ON dbo.TMPVoucher.TMPVoucherOrderID = dbo.TMPVoucherOrder.TMPVoucherOrderID INNER JOIN
                      dbo.TMPServerOrder ON dbo.TMPVoucher.TMPServerOrderID = dbo.TMPServerOrder.TMPServerOrderID INNER JOIN
                      dbo.TSSMerchant ON dbo.TMPVoucher.TSSMerchantID = dbo.TSSMerchant.TSSMerchantID INNER JOIN
                      dbo.TMPVendor ON dbo.TMPVoucherType.TMPVendorID = dbo.TMPVendor.TMPVendorID INNER JOIN
                      dbo.TMPVoucherOrderDetail ON dbo.TMPVoucherType.TMPVoucherTypeID = dbo.TMPVoucherOrderDetail.TMPVoucherTypeID AND
                      dbo.TMPVoucherOrder.TMPVoucherOrderID = dbo.TMPVoucherOrderDetail.TMPVoucherOrderID
WHERE     (dbo.TMPVoucherOrder.OrderNo = @orderNumber);
END

No comments:

Post a Comment