标注的SQL拼接语句

方案一

exec sp_executesql N'
       SELECT T0.[WtmCode], T3.[opCode], T3.[opValue], T3.[CondId]
        FROM  [dbo].[OWTM] T0  INNER  JOIN [dbo].[WTM1] T1  ON  T1.[WtmCode] = T0.[WtmCode]   
        INNER  JOIN [dbo].[WTM3] T2  ON  T2.[WtmCode] = T0.[WtmCode]   
        INNER  JOIN [dbo].[WTM4] T3  ON  T3.[WtmCode] = T0.[WtmCode]   
        WHERE T1.[UserID] = (@P1)  AND  T2.[TransType] = (@P2)  AND  T3.[CondId] = (@P3)  
        AND  T0.[Conds] = (@P4)  AND  T0.[Active] = (@P5)  '
        ,N'@P1 int,@P2 nvarchar(254),@P3 int,@P4 nvarchar(254),@P5 nvarchar(254)',1,N'17',1,N'Y',N'Y'

 

 

 

方案二

IF @UserCmpType='D'
        BEGIN
            SET @sameContent='FROM dmsSaleOPORPJD  T1 '
            SET @sameContent2='FROM dmsSaleOPORPJD1  T1 '
            SET @sameContent3='FROM dmsSaleOPORPJD2  T1 '
            SET @sameContent4='FROM dmsSaleOPORPJD3  T1 '
        END
        
        ELSE IF @UserCmpType='R'
        BEGIN
            SET @sameContent='FROM dmsSaleOPORPJR  T1 '
            SET @sameContent2='FROM dmsSaleOPORPJR1  T1 '
            SET @sameContent3='FROM dmsSaleOPORPJR2  T1 '
            SET @sameContent4='FROM dmsSaleOPORPJR3  T1 '
        END
        
        IF @ActionGroup='Approval'
        BEGIN
            SET @sameContent1='T1.APPStatus=''正在审批'''
        END
        
        ELSE IF @ActionGroup='ToDelivery'
        BEGIN
            SET @sameContent1='T1.APPStatus=''已通过'' AND T1.[Status]=''待发货'''
        END
        
        
        IF @ActionType='Main'
        BEGIN
            SET @SQL='
            SELECT 
            T1.ID [Id]
            ,T1.DOCENTRY [DocEntry] 
            ,T1.CardName [CardName]
            ,CONVERT(NVARCHAR(10),T1.DocDay,23) [DocDay]
            ,convert(decimal(18,2),T1.RecDocTotal) [RecDocTotal]
            ,CASE ISNULL(T1.ShipAddress,'''') WHEN '''' THEN T1.CollectLogistics 
                ELSE T1.ShipAddress END [ShipAddress]
            '+@sameContent+'
            WHERE  '+@sameContent1+'
            AND T1.CmpCode=@UserCmpCode
            ORDER BY T1.DocDay ASC
            '
        END

 

EXEC SP_EXECUTESQL @stmt=@SQL,@params=N'@ActionGroup NVARCHAR(50),@UserCmpCode NVARCHAR(50),@Id INT'
        ,@ActionGroup=@ActionGroup,@UserCmpCode=@UserCmpCode,@Id=@Id

 

posted @ 2017-07-27 17:24  淘小人  阅读(509)  评论(0编辑  收藏  举报