SQL 函数返回table

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER FUNCTION [dbo].[Fun_ShengJiWenMingGongDiShenQingTiaoJian]
--参数
(@BelongedTo varchar(200),@RecordNumber varchar(100))
--返回表
RETURNS @RetDataTable TABLE (
[ID] INT
,[content] NVARCHAR(100)
,[states] NVARCHAR(100)
,[isFuHe] NVARCHAR(100)
,[solution] NVARCHAR(100)
)
AS
BEGIN
DECLARE @state NVARCHAR(100),@val NVARCHAR(100),@demoNum INT ,@idx INT=0,@ProjectCategory NVARCHAR(200),@solution NVARCHAR(100)

SELECT @ProjectCategory=ProjectCategory FROM dbo.ProjectOverview WHERE BelongedTo=@BelongedTo AND RecordNumber=@RecordNumber

-- 1找出备案号下的该项目的申报目标
SET @idx = @idx+1
SELECT @state = ISNULL(ProjectTarget,'')
FROM projectoverview
WHERE BelongedTo =@BelongedTo and RecordNumber=@RecordNumber
IF @state IN ('省级文明工地','省级标准化示范工地','国家3A级工地','国家3A级示范工地')
SELECT @val = '符合',@solution='无'
ELSE
SELECT @val = '不符合',@solution='请联系工程所属安监站的安全监督备案资料员进行创建目标变更'
INSERT INTO @RetDataTable(ID,content,states,isFuHe,solution)
VALUES(@idx,'创建目标符合',@state,@val,@solution)


--是否是市级文明工程
SET @idx = @idx+1
SELECT @demoNum = COUNT(1) FROM dbo.CityCivilizationApply
WHERE BelongedTo=@BelongedTo AND OldRecordNumber=@RecordNumber AND ExamResults IN ('合格','通过')
IF @demoNum <= 0 OR @demoNum IS NULL
SELECT @val = '不符合',@state='市级文明尚未通过'
ELSE
SELECT @val = '符合',@state='市级文明已通过'
INSERT INTO @RetDataTable(ID,content,states,isFuHe,solution)
VALUES(@idx,'是否是市级文明工程',@state,@val,@solution)

SET @idx = @idx+1
SELECT @demoNum = COUNT(1)
FROM AccidentRegister AS ar
LEFT JOIN AccidentBulletin AS ab ON ar.BelongedTo = ab.BelongedTo AND ar.AccidentNo = ab.AccidentNo AND ar.RecordNumber = ab.RecordNumber
WHERE ar.BelongedTo=@BelongedTo AND ar.RecordNumber=@RecordNumber AND AccidentType <> '猝死'
AND ab.AccidentResponsibleUnits IN (
SELECT EnterpriseName FROM dbo.ProjectEntSnapshot
WHERE BelongedTo=@BelongedTo AND RecordNumber=@RecordNumber
AND MainUnit='是'
)
IF @demoNum <= 0 OR @demoNum IS NULL
SELECT @val = '符合',@state='无事故'
ELSE
SELECT @val = '不符合',@state='存在事故'
INSERT INTO @RetDataTable(ID,content,states,isFuHe,solution)
VALUES(@idx,'是否存在事故',@state,@val,@solution)

-- 4找出备案号下是否有行政处罚，且处罚结果上报，但处罚还未通过期间内的不允许申报
SET @idx = @idx+1
SELECT @demoNum=COUNT(1) FROM PenalizeCaseAudit
WHERE BelongedTo=@BelongedTo AND RecordNumber=@RecordNumber
AND PunishDate IS NOT NULL --有行政处罚时间的
AND PunishDate <> ''
and ISNULL(DataState,'') <>'del'
AND DATEDIFF(DAY,PunishDate,GETDATE()) <=0
AND EntCode=(SELECT DISTINCT(OrganizationCode)
FROM ProjectEntSnapshot
WHERE RecordNumber=@RecordNumber AND BelongedTo=@BelongedTo AND
EnterpriseType='施工单位' and MainUnit='是')
IF @demoNum <= 0
SELECT @val = '符合',@state='无行政处罚'
ELSE
SELECT @val = '不符合',@state='存在行政处罚'
INSERT INTO @RetDataTable(ID,content,states,isFuHe,solution)
VALUES(@idx,'是否有行政处罚',@state,@val,@solution)

-- 5找出该单位在此项目下是否有挂红牌，且未解牌的则不允许申报
SET @idx = @idx+1
IF EXISTS (SELECT * FROM BookingsApproval
WHERE BelongedTo=@BelongedTo AND RecordNumber=@RecordNumber
AND ViolationType IN (
SELECT EnterpriseName FROM dbo.ProjectEntSnapshot
WHERE BelongedTo=@BelongedTo AND RecordNumber=@RecordNumber
AND MainUnit='是'
)
AND BookingsType ='红牌' AND GETDATE() BETWEEN BookingsBeginDate AND BookingsEndDate)
SELECT @val = '不符合',@state='挂红牌'
ELSE
SELECT @val = '符合',@state='当前无挂牌'
INSERT INTO @RetDataTable(ID,content,states,isFuHe,solution)
VALUES(@idx,'是否有挂红牌',@state,@val,@solution)

RETURN
END
GO