sqlserver动态sql

动态sql中调用函数(return value and table)demo

declare
 @TempFactoryID nvarchar(50)='aba|dff', 
 @query nvarchar(200) ='select * from [dbo].[fn_Split](@TempFactoryID, ''|'')',
 @par   nvarchar(30) ='@TempFactoryID nvarchar(50)'
EXEC Sp_executesql @query, @par, @TempFactoryID

一次开发dppm值经历
需求如下代码：

USE [efoxsfclxhp]
GO
/****** Object:  StoredProcedure [dbo].[Web_dppm_oper]    Script Date: 2020/9/13 08:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Web_dppm_oper] (@strTransType VARCHAR(30) = NULL,
                                       @FactoryID    VARCHAR(30)= NULL,
                                       @PartNo       VARCHAR(2000)=NULL,
                                       @WorkOrderNo  VARCHAR(2000) =NULL,
                                       @EventPoint   VARCHAR(50)=NULL,
                                       @Categoryname VARCHAR(30)= NULL,
                                       @DateS        VARCHAR(50)=NULL,
                                       @dateE        VARCHAR(50)=NULL)
AS
    DECLARE @SqlTable TABLE
      (
         parameter VARCHAR(200),
         Orderr    VARCHAR(5),--顺序
         SQLmess   VARCHAR(2000)--sql内容
      )

  BEGIN
      IF @strTransType = 'seach'
        BEGIN
            DECLARE @AsstTable TABLE
              (
                 partno VARCHAR(200),
                 num    INT
              )
            DECLARE @RepairTable TABLE
              (
                 failurepartno VARCHAR(200),
                 num           INT
              )
            DECLARE @QueryString      NVARCHAR(2000),
                    @paramstring      NVARCHAR(500),
                    @TempFactoryID    NVARCHAR(30),
                    @TempWorkOrderNo  NVARCHAR(1000),
                    @TempPartNo       NVARCHAR(1000),
                    @TempEventPoint   NVARCHAR(30),
                    @TempCategoryname NVARCHAR(30),
                    @TempDateS        NVARCHAR(30),
                    @TempDateE        NVARCHAR(30)

            SET @paramstring = '@FactoryID nvarchar(30),@WorkOrderNo nvarchar(1000),@PartNo nvarchar(1000),@EventPoint nvarchar(30),@Categoryname nvarchar(30),@DateS nvarchar(30),@DateE nvarchar(30)'
            SET @TempFactoryID =CASE WHEN Isnull(@FactoryID, '') = '' THEN '' ELSE @FactoryID END
            SET @TempWorkOrderNo = CASE WHEN Isnull(@WorkOrderNo, '') = '' THEN '' ELSE @WorkOrderNo END
            SET @TempPartNo = CASE WHEN Isnull(@PartNo, '') = '' THEN '' ELSE @PartNo END
            SET @TempEventPoint = CASE WHEN Isnull(@EventPoint, '') = '' THEN '' ELSE @EventPoint END
            SET @TempCategoryname =CASE WHEN Isnull(@Categoryname, '') = '' THEN '' ELSE @Categoryname END
            SET @TempDateS = CASE WHEN Isnull(@DateS, '') = '' THEN '' ELSE @DateS END
            SET @TempDateE = CASE WHEN Isnull(@dateE, '') = '' THEN '' ELSE @dateE END

            INSERT @SqlTable --
            SELECT 'mfsyscserial',0, N' SELECT a.partno, Count(1) num FROM mfsyscserial a WHERE 1=1 '
            UNION
            SELECT '@FactoryID',1,CASE WHEN Isnull(@FactoryID, '') = '' THEN ' and @FactoryID = @FactoryID ' ELSE N' AND EXISTS(select null from mfsysproduct t where t.sysserialno = a.sysserialno and  t.factoryid = @FactoryID) ' END
            UNION
            SELECT '@WorkOrderNo', 5, CASE WHEN Isnull(@WorkOrderNo, '') = '' THEN ' and @WorkOrderNo = @WorkOrderNo ' ELSE N' and EXISTS(SELECT NULL FROM mfworkstatus b 
			WHERE b.sysserialno = a.sysserialno AND LEFT(b.sysserialno, 1) <> ''~'' AND EXISTS(SELECT NULL FROM Fn_split(@WorkOrderNo, ''|'') WHERE  Value = b.workorderno)) ' END
            UNION
            SELECT '@PartNo', 10, CASE WHEN Isnull(@PartNo, '') = '' THEN ' and @PartNo = @PartNo ' ELSE N' AND partno IN(SELECT value FROM Fn_split(@PartNo, ''|'')) ' END
            UNION
            SELECT '@EventPoint', 15, CASE WHEN Isnull(@EventPoint, '') = '' THEN ' and @EventPoint = @EventPoint ' ELSE ' and @EventPoint = @EventPoint ' END
			--N' AND EXISTS(select null from Sfcrepairmain m where m.sysserialno = a.sysserialno and m.failureeventpoint = @EventPoint and EXISTS(select null from sfcrepairpart r where r.sysserialno = m.sysserialno and r.failurepartno = a.partno and m.lineseqno = r.lineseqno)) '
            UNION
            SELECT '@Categoryname',20,CASE WHEN Isnull(@Categoryname, '') = '' THEN ' and @Categoryname = @Categoryname ' ELSE N' AND a.categoryname = @Categoryname ' END
            UNION
            SELECT '@DateS', 25, CASE WHEN Isnull(@DateS, '') = '' THEN ' and @DateS = @DateS ' ELSE N' AND a.lasteditdt >= @DateS ' END
            UNION
            SELECT '@DateE', 30, CASE WHEN Isnull(@DateE, '') = '' THEN ' and @DateE = @DateE ' ELSE N' AND a.lasteditdt <= @dateE ' END
            UNION
            SELECT 'groupBy', 50, ' GROUP  BY partno'

            SELECT @QueryString = Stuff((SELECT '' + t.SQLmess
                                         FROM   (SELECT SQLmess,
                                                        Orderr
                                                 FROM   @SqlTable) t
                                         ORDER  BY t.Orderr --条件排序
                                         FOR xml path('')), 1, 1, '')

            --&#x0D; 回车        --&gt;>         --&lt;<
            SELECT @QueryString = Replace(Replace(Replace(@QueryString, '&#x0D;', ''), '&gt;', '>'), '&lt;', '<')

            INSERT INTO @AsstTable
            EXEC Sp_executesql @QueryString, @paramstring, @TempFactoryID, @TempWorkOrderNo, @TempPartNo, @TempEventPoint, @TempCategoryname,
              @TempDateS, @TempDateE --这些参数必须nvarchar类型
            --select * from @AsstTable
            DELETE @SqlTable

            INSERT @SqlTable
            SELECT 'sfcrepairpart', 0, N' SELECT failurepartno,Count(1) num FROM sfcrepairpart r LEFT JOIN Sfcrepairmain m
			ON r.sysserialno = m.sysserialno AND r.lineseqno = m.lineseqno inner join mmprodmaster p on p.partno = r.failurepartno WHERE  LEFT(m.sysserialno, 1) <> ''~'' '
            UNION
            SELECT '@FactoryID', 1, CASE WHEN Isnull(@FactoryID, '') = '' THEN ' and @FactoryID = @FactoryID ' ELSE N' AND m.factoryid = @FactoryID 'END
            UNION
            SELECT '@WorkOrderNo',5,CASE WHEN Isnull(@WorkOrderNo, '') = '' THEN ' and @WorkOrderNo = @WorkOrderNo ' ELSE N' and EXISTS(SELECT NULL FROM mfworkstatus b 
			WHERE b.sysserialno = m.sysserialno AND LEFT(b.sysserialno, 1) <> ''~'' AND EXISTS(SELECT NULL FROM Fn_split(@WorkOrderNo, ''|'') WHERE  Value = b.workorderno)) 'END
            UNION
            SELECT '@PartNo',10, CASE WHEN Isnull(@PartNo, '') = '' THEN ' and @PartNo = @PartNo ' ELSE N' and failurepartno IN(SELECT value FROM   Fn_split(@PartNo, ''|'')) ' END
            UNION
            SELECT '@EventPoint',15,CASE WHEN Isnull(@EventPoint, '') = '' THEN ' and @EventPoint = @EventPoint ' ELSE N' AND m.failureeventpoint = @EventPoint ' END
            UNION
            SELECT '@Categoryname',20,CASE  WHEN Isnull(@Categoryname, '') = '' THEN ' and @Categoryname = @Categoryname ' ELSE N' AND p.categoryname = @Categoryname ' END
            UNION
            SELECT '@DateS',25,CASE WHEN Isnull(@DateS, '') = '' THEN ' and @DateS = @DateS ' ELSE N' AND m.lasteditdt >= @DateS 'END
            UNION
            SELECT '@DateE', 30, CASE WHEN Isnull(@DateE, '') = '' THEN ' and @DateE = @DateE ' ELSE N' AND m.lasteditdt <= @dateE ' END
            UNION
            SELECT 'groupBy', 50,' GROUP  BY failurepartno'

            SELECT @QueryString = Stuff((SELECT '' + t.SQLmess
                                         FROM   (SELECT SQLmess,
                                                        Orderr
                                                 FROM   @SqlTable) t
                                         ORDER  BY t.Orderr --条件排序
                                         FOR xml path('')), 1, 1, '')

            --&#x0D; 回车        --&gt;>         --&lt;<
            SELECT @QueryString = Replace(Replace(Replace(@QueryString, '&#x0D;', ''), '&gt;', '>'), '&lt;', '<')

            INSERT INTO @RepairTable
            EXEC Sp_executesql
              @QueryString,
              @paramstring,
              @TempFactoryID,
              @TempWorkOrderNo,
              @TempPartNo,
              @TempEventPoint,
              @TempCategoryname,
              @TempDateS,
              @TempDateE --这些参数必须nvarchar类型
            --select * from @RepairTable
            SELECT a.partno,
                   a.num                                                              Total,
                   CASE WHEN Isnull(b.num, '') = '' THEN '0' ELSE b.num END           FA,
                   Cast(b.num * 1.0 / ( a.num + b.num ) AS DECIMAL(18, 10)) * 1000000 dppm,
                   ''                                                                 details
            FROM   @AsstTable a LEFT JOIN @RepairTable b ON a.partno = b.failurepartno
            ORDER  BY dppm DESC
        END

      IF @strTransType = 'Export'
        BEGIN
            SELECT @strTransType
        END
  END