存储过程调用函数

1、

USE [DB_MES2]

GO
/****** Object:  UserDefinedFunction [dbo].[GetProductCopyFuncIsHasAllocated]    Script Date: 2017/7/12 9:36:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetProductCopyFuncIsHasAllocated] (@Product_ID uniqueidentifier)
RETURNS int
AS
BEGIN
DECLARE @Result int
DECLARE @Result2 int
SELECT @Result =COUNT(*) from MES_PRODUCT_PART p
   join MES_PRODUCT_STEP s on p.PRODUCT_STEP_ID=s.PRODUCT_STEP_ID
join MES_PRODUCT_PROCESS ps on s.PRODUCT_PROCESS_ID=ps.PRODUCT_PROCESS_ID 
join MES_PRODUCT_FLOW pf on ps.PRODUCT_FLOW_ID= pf.PRODUCT_FLOW_ID
   where PRODUCT_ID = @Product_ID
if(@Result>0)
      set @Result2=1   
else 
 set  @Result2=0
RETURN @Result2
END


--测试
DECLARE @Result int
EXEC @Result=[GetProductCopyFuncIsHasAllocated] '4ed7000c-e123-41ba-a31b-8f1216b91922'

print @Result

2、

USE [DB_MES2]
GO
/****** Object:  UserDefinedFunction [dbo].[GetProductCopyFunc]    Script Date: 2017/7/12 9:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetProductCopyFuncIsHasCountersign] (@Product_ID uniqueidentifier)
RETURNS int
AS
BEGIN
DECLARE @Result int
DECLARE @Result2 int
SELECT @Result = COUNT(*)  from SYS_COUNTERSIGN_REQ where PRODUCT_ID = @Product_ID
if(@Result>0)
      set @Result2=1   
else 
 set  @Result2=0
RETURN @Result2
END

3、

USE [DB_MES2]
GO
/****** Object:  UserDefinedFunction [dbo].[GetProductCopyFuncIsHasCad]    Script Date: 2017/7/12 9:34:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetProductCopyFuncIsHasCad] (@Product_ID uniqueidentifier)
RETURNS int
AS
BEGIN
DECLARE @Result int
DECLARE @Result2 int
SELECT @Result = COUNT(*)  from SYS_PRODUCT_DOC where PRODUCT_ID = @Product_ID
if(@Result>0)
      set @Result2=1   
else 
 set  @Result2=0
RETURN @Result2
END
--测试
--DECLARE @Result int
--EXEC @Result=[GetProductCopyFuncIsHasCad] '4ed7000c-e123-41ba-a31b-8f1216b91922'
--print @Result

4、

USE [DB_MES2]
GO
/****** Object:  UserDefinedFunction [dbo].[GetProductCopyFuncIsHasAllocated]    Script Date: 2017/7/12 9:51:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetProductCopyFuncIsHasProcess] (@Product_ID uniqueidentifier)
RETURNS int
AS
BEGIN
DECLARE @Result int
DECLARE @Result2 int
SELECT @Result = COUNT(*) from MES_PRODUCT_FLOW where PRODUCT_ID = @Product_ID 
if(@Result>0)
      set @Result2=1   
else 
 set  @Result2=0
RETURN @Result2
END
--测试
--DECLARE @Result int
--EXEC @Result = [GetProductCopyFuncIsHasProcess] '4ed7000c-e123-41ba-a31b-8f1216b91922'
--PRINT @Result

5、

USE [DB_MES2]
GO
/****** Object:  UserDefinedFunction [dbo].[GetProductCopyFuncIsHasBom]    Script Date: 2017/7/12 9:32:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetProductCopyFuncIsHasBom] (@Product_ID uniqueidentifier)
RETURNS int
AS
BEGIN
DECLARE @Result int
DECLARE @Result2 int
SELECT @Result = COUNT(*)  from SYS_PRODUCT_BOM where PRODUCT_ID = @Product_ID
if(@Result>0)
      set @Result2=1   
else 
 set  @Result2=0
RETURN @Result2
END


--测试
--DECLARE @Result int
--EXEC @Result=[GetProductCopyFuncIsHasBom] '4ed7000c-e123-41ba-a31b-8f1216b91922'
--print @Result

6、总的调用

USE [DB_MES2]
GO
/****** Object:  StoredProcedure [dbo].[GetProductCopy]    Script Date: 2017/7/10 8:59:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: <Author,王林杰>
-- Create date: <Create Date,2017/07/10>
-- Description: <Description,根据产品id获取产品复制的所有相关信息>
-- =============================================
ALTER PROCEDURE  [dbo].[GetProductCopy] 
@Product_ID uniqueidentifier 
AS
BEGIN
DECLARE @ResultCountersign int
DECLARE @ResultBom int
DECLARE @ResultAllocated int
DECLARE @ResultCad int
DECLARE @ResultProcess int


    --判断是否会签
    EXEC @ResultCountersign=GetProductCopyFuncIsHasCountersign @Product_ID
--print @ResultCountersign
--判断是否分配Bom
EXEC @ResultBom=GetProductCopyFuncIsHasBom @Product_ID
--print @ResultBom
--判断是否分配Cad
EXEC @ResultCad=GetProductCopyFuncIsHasCad @Product_ID
--print @ResultCad
--判断是否设置流程
EXEC @ResultProcess = [GetProductCopyFuncIsHasProcess] @Product_ID
    ---print @ResultProcess
--判断是否分配元件
EXEC @ResultAllocated=GetProductCopyFuncIsHasAllocated @Product_ID
--print @ResultAllocated


SELECT a.PRODUCT_ID,a.PRODUCT_NO,a.PRODUCT_NAME,a.VER,a.SPEC,a.CUSTOMER,a.CATEGORY,a.STATUS,a.DESCRIPTION,a.OTHER1,a.OTHER2,a.OTHER3,a.OTHER4,
      a.MK_USER_ID,a.COUNTERSIGN_CODE,PUBDATE,a.STOPDATE,a.REPLACE_MODE,
  @ResultBom as IsHasBom,
  @ResultAllocated as IsHasAllocated,
  @ResultCad as IsHasCad,
  @ResultCountersign as IsHasCountersign ,
  @ResultProcess as IsHasProcess 
  FROM SYS_PRODUCT a WHERE PRODUCT_ID = @Product_ID


--判断是否分配Bom
if(@ResultBom>0)
   SELECT * FROM SYS_PRODUCT_BOM WHERE PRODUCT_ID = @Product_ID


--判断是否分配Cad
if(@ResultCad>0)
   SELECT * FROM SYS_PRODUCT_DOC WHERE PRODUCT_ID = @Product_ID

--判断是否会签
if(@ResultCountersign>0)
   SELECT * FROM SYS_COUNTERSIGN_REQ WHERE PRODUCT_ID = @Product_ID
    
--判断是否设置流程
if(@ResultProcess>0)
   SELECT * FROM MES_PRODUCT_FLOW WHERE PRODUCT_ID = @Product_ID


--判断是否分配元件
if(@ResultAllocated>0)
   SELECT * from MES_PRODUCT_PART p
   join MES_PRODUCT_STEP s on p.PRODUCT_STEP_ID=s.PRODUCT_STEP_ID
join MES_PRODUCT_PROCESS ps on s.PRODUCT_PROCESS_ID=ps.PRODUCT_PROCESS_ID 
join MES_PRODUCT_FLOW pf on ps.PRODUCT_FLOW_ID= pf.PRODUCT_FLOW_ID
   where PRODUCT_ID = @Product_ID
    --产品替代料
        SELECT * from MES_BOM_REPLACE where PRODUCT_ID = @Product_ID

END


--EXEC [GetProductCopy] '4ed7000c-e123-41ba-a31b-8f1216b91922'


7、程序调用方式

 SqlParameter parameter = new SqlParameter("@Product_ID", System.Data.SqlDbType.UniqueIdentifier);
            parameter.SqlValue = ProductId;


            var ss = Repository.ExecuteQuerySqlToDataSet("EXEC [GetProductCopy] @Product_ID", parameter);（本作者使用的是ef实体）