LK_Project01

MS SQLตัวอย่าง Store Procedure RegisterCustomer
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SP_BL_Get_RegisterCustomer]
-- Add the parameters for the stored procedure here
@custcode as nvarchar(max)='',
@admincustcode as nvarchar(max)='',
@packageType as nvarchar(max)='',
@product as nvarchar(max)='',
@MID as nvarchar(10)='',
@Cancel as nvarchar(10)='',
@Rent as bit,
@Service as bit,
@Suspend as bit,
@DealerCode as nvarchar(200),
@Status as nvarchar(max)=null,
@SimNo as nvarchar(20)=null,
@VehicleID as nvarchar(20)=null,
@Install as int=0,
@DocStatus as nvarchar(100)=null,
@PO as nvarchar(255)=null,
@SO as nvarchar(255)=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if (@custcode is null)
set @custcode=''
if (@admincustcode is null)
set @admincustcode=''
if (@packageType is null)
set @packageType=''
if (@product is null)
set @product=''
if (@MID is null)
set @MID=''
if (@Rent is null)
set @Rent=0
if (@Service is null)
set @Service=0
if (@Suspend is null)
set @Suspend=0
if (@DealerCode is null)
set @DealerCode=''
if (@SimNo is null)
set @SimNo=''
if (@VehicleID is null)
set @VehicleID=''
if (@Status is null)
set @Status=''
if (@Cancel IS NULL)
Set @Cancel =''
if (@Install IS NULL)
SET @Install=0
if (@DocStatus IS NULL)
SET @DocStatus=''
if (@PO IS NULL)
SET @PO=''
if (@SO IS NULL)
SET @SO=''

--SELECT * into #tempDBWins FROM dbwins_onelink.dbo.EMCust--EmCust_DbWins
-- Insert statements for procedure here
Declare @SqlCommand nvarchar(max)
SET @SqlCommand= 'SELECT em.No1,em.CustCode,em.CustName,
em.AdminCustCode,em.AdminCustName,
em.PackageType,em.Product,em.Service,
em.Rent,em.GoodPrice,em.GoodCode,
em.GoodName,em.RentPrice,
em.RentCurr,em.RentEnd,
em.MID,
em.SimCardNo,
em.RentGoodCode,
em.RentGoodName,
em.InstallDate,
em.DateOfCharge,
em.BillingEndDateCurr,
em.Cancel,
em.CancelDate,
em.CancelBy,
--ISNULLas EmpID,
--ISNULLas EmpName,
0 AS EmpID,
'''' AS EmpName,
em.RefDocuNo1,em.RefDocuNo2,
em.Suspend,
em.DealerCode,
em.DealerName,
em.ApproveBill,
em.ApproveBillBy,
em.ApproveBillDate,
em.ISNo,
em.IVNo,
em.SONo,
em.DocuStatus,
em.KeyinBy,
em.CreateDate,
em.UpdateBy,
em.UpdateDate,
em.GroupPages,
em.DiscAmnt,
em.DiscRema,
em.RentDiscAmnt,
em.RentDiscRema,
em.VehicleID,
ISNULLCustomerVerify,
em.CustomerVerifyDate,
em.CustomerVerifyBy
FROM EmCustRegister em
left join dbwins_onelink.dbo.EMCust ec on em.CustCode=ec.CustCode
--left join EMEmp ee on ec.SaleCode=ee.EmpCode
--LEFT JOIN #tempDBWins ec on em.CustCode=ec.CustCode
where
ISNULL in
(
select LTRIM) from dbo.[ufn_CSVToTable]('''+@packageType+''')
)
and ISNULL in
(
select LTRIM) from dbo.[ufn_CSVToTable]('''+@product+''')
)
and Cancel in
(
select CONVERT)) from dbo.[ufn_CSVToTable]('''+@Cancel+''')
)

';
        --ISNULL(ec.CustCode,'''') like '''+@custcode+''' 
        --and ISNULL(AdminCustCode,'''') like '''+@admincustcode+''' 
        --and ISNULL(DealerCode,'''') like '''+@DealerCode+'''    
        --and ISNULL(MID,'''') like '''+@MID+''' 
        IF (@custcode IS NOT NULL AND @custcode<>'')
            SET @SqlCommand=@SqlCommand+' and ISNULL(em.CustCode,'''') like('''+@custcode+''')'
        IF (@admincustcode IS NOT NULL AND @admincustcode<>'')
            SET @SqlCommand=@SqlCommand+' and ISNULL(em.AdminCustCode,'''') like('''+@admincustcode+''')'
        IF (@DealerCode IS NOT NULL AND @DealerCode<>'')
            SET @SqlCommand=@SqlCommand+' and ISNULL(DealerCode,'''') like('''+@DealerCode+''')'
        IF (@MID IS NOT NULL AND @MID<>'')
            SET @SqlCommand=@SqlCommand+' and ISNULL(MID,'''') like('''+@MID+''')'
        IF (@Status IS NOT NULL And @Status<>'')
            SET @SqlCommand=@SqlCommand+'and ISNULL(em.ApproveBill,'''') in
            ( 
                select CONVERT(bit,LTRIM(RTRIM(String))) from dbo.[ufn_CSVToTable]('''+@Status+''')
            )';
        --Install
        IF (@Install=2)
            SET @SqlCommand=@SqlCommand+' and em.InstallDate is not null and em.InstallDate <>'''''
        --Not Install
        ELSE IF(@Install=1)
            SET @SqlCommand=@SqlCommand+' and em.InstallDate is null '
        IF (@SimNo<>'')
            SET  @SqlCommand=@SqlCommand+' and em.SimCardNo like(''%'+@SimNo+'%'')'
        IF (@VehicleID<>'')
            SET @SqlCommand=@SqlCommand+' and em.VehicleID like('''+@VehicleID+''')'
        IF (@PO<>'')
            SET @SqlCommand=@SqlCommand+' and em.RefDocuNo1 like('''+@PO+''')'
        IF (@SO<>'')
            SET @SqlCommand=@SqlCommand+' and em.SONo like('''+@SO+''')'
        IF (@DocStatus<>'')
            SET    @SqlCommand=@SqlCommand+'and ISNULL(em.DocuStatus,'''') in
            ( 
                select LTRIM(RTRIM(String)) from dbo.[ufn_CSVToTable]('''+@DocStatus+''')
            )';         
        SET @SqlCommand=@SqlCommand+'order by CustName,AdminCustName,MID'
        EXEC(@SqlCommand) 
        --DROP TABLE #tempDBWins
        --select @SqlCommand
END